Resolved: 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.

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.

If you have better answer, please add a comment about this, thank you!

Resolved: All records with Pivot table

Resolved: Java Swing center JLabel in JPanel with two JLabels

Resolved: How to compare two text files and and change the sign of the data in powershell?

Resolved: Java Virtual Machines deleted

Leave A Reply