All records with Pivot table

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,

SELECT *
FROM   (
  select 
  ORDER_ID, 
 'Report' + cast((dense_rank() OVER (  ORDER BY order_number) - 1) % 3 + 1) as varchar)) + '_ID' ORDER_S_NO 
  from OrderTable
)main

PIVOT ( max(ORDER_ID) FOR ORDER_S_NO IN (Report1_ID,Report2_ID,Report3_ID) ) AS pivotTable;

Thanks in advance.

user_1863229 · Answer

Rather than using the modulus three, you should be using the remainder with a divisor of three. But I would avoid PIVOT here and just use a regular aggregation:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (ORDER BY order_number) rn
    FROM yourTable t
)

SELECT ROW_NUMBER() OVER (ORDER BY (rn - 1) / 3) AS Serial_NO,
       MAX(Report1_ID) AS Report1_ID,
       MAX(Report2_ID) AS Report2_ID,
       MAX(Report3_ID) AS Report3_ID
FROM cte
GROUP BY (rn - 1) / 3
ORDER BY (rn - 1) / 3;

Here is a demo showing that the above query is working. Note that in the demo I don't have an actual order_number column, 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.

All records with Pivot table

1 Answers