Question:I have table as below after pivoting,
Order_ID Report1_ID Report2_ID Report3_ID Order_1 OR_1 null null Order_2 null OR_2 null Order_3 null null OR_3 Order_4 OR_4 null null Order_5 null OR_5 null Order_6 null null OR_6 Order_7 OR_7 null null Order_8 null OR_8 null Order_9 null null OR_9
I need like,
Serial_NO Report1_ID Report2_ID Report3_ID 1 OR_1 OR_2 OR_3 2 OR_4 OR_5 OR_6 3 OR_7 OR_8 OR_9
I need to remove all the null values and list like above table. My Query as below,
Answer:Rather than using the modulus three, you should be using the remainder with a divisor of three. But I would avoid
PIVOThere and just use a regular aggregation:
order_numbercolumn, but instead just used
ORDER_ID. Coincidentally, this works fine, but would not work for more than 9 records, as text numbers don’t sort the same way as actual numbers.
If you have better answer, please add a comment about this, thank you!