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 will point out that your datamodel need some improvement, so my method to join serial is improvised and can't be trusted in a real life project, there is no logical connection between your tables. Here is how to pivot your data:

;WITH [rows] AS
(
  SELECT id, value, col_id
  FROM 
    (values
    (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)) x(Id, col_id, value)
), cols as
(
  SELECT cols.id, col_name
  FROM
    (values
      ( 2, 'Data1'),( 3, 'Data2')) 
      cols(Id, col_name)
), result as
(
  SELECT row_number() OVER (partition by col_id order by [rows].id) rn, col_name, value
  FROM
    cols
  JOIN
    [rows]
  ON [rows].col_id = cols.id
  WHERE cols.id > 1
)
SELECT rn Serial, Data1, Data2
FROM
  result
PIVOT
(min([value])  
FOR col_name
in([Data1],[Data2])  
)AS p

Result:

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

Answer 2

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