TSql Pivot or Rows to Columns

Question

I have seen various posts on Pivot or converting rows to columns, but still i have not luck with it so i have decided to post the question.

My table cols is

Id col_name 1 Serial 2 Data1 3 Data2

My table rows is

Id col_id value 1 1 1 2 1 2 3 1 3 4 2 25 5 2 26 6 2 27 7 3 28 8 3 29 9 3 30

I want the result as below i think using unpivot or pivot might work. The out come should be something like -

Serial Data1 Data2 1 25 28 2 26 29 3 27 30

Any help would be helpful.

Answer 1

I don't think pivot is useful here. Try this

WITH cte1
     AS (SELECT Row_number()OVER(ORDER BY Id) RN,
                value            AS Data1
         FROM   yourtable
         WHERE  col_id = 1),
     cte2
     AS (SELECT Row_number()OVER(ORDER BY Id) RN,
                value            AS Data2
         FROM   yourtable
         WHERE  col_id = 2),
     cte3
     AS (SELECT Row_number()OVER(ORDER BY Id) RN,
                value            AS Data3
         FROM   yourtable
         WHERE  col_id = 3)
SELECT Row_number()OVER(ORDER BY a.rn) AS serial,
       Data1,
       Data2,
       Data3
FROM   cte1 A
       FULL OUTER JOIN cte2 B
                    ON a.RN = b.RN
       FULL OUTER JOIN cte3 C
                    ON b.RN = C.RN