CODEWITHSUNDEEP
sql-servert-sqlpivottranspose
Ben Z.
Ben Z.

Reputation: 459

Transpose / Pivot rows to columns

I am trying to transpose (Pivot?) a table. This is my current setup.
Current Table:

ID | Value
1  | 10
1  | 11
1  | 12
1  | 13
1  | 14
2  | 123
3  | 13423
3  | 1134
3  | 1234

Seeking the following result:

ID | Value01 | Value 02 | Value 03 | Value 04 | Value 05
1  |  10     |  11      |   12     |   13     |  14
2  |  123
3  | 13423   | 1134     | 1234

Currently I am trying it with PIVOT however I am not completely sure how to PIVOT without a "category column" (such as days, or months). Could I use the ID column for this?

SELECT ID, Value, [0], [1], [2], [3], [4] 
FROM (
      SELECT ID, Value FROM dbo.TABLE
) SourceTable 
PIVOT (
      VALUE FOR ID IN ([0], [1], [2], [3], [4])
) AS PivotTable

There is no preset amount of VALUE's for each ID. But if it is required to have a known number, 5 values (and thus 5 columns) is enough.

Upvotes: 0

Views: 3471

Answers (1)

Taryn
Taryn

Reputation: 247860

Your current query is close, you are missing the value that you want as the new column names. My suggestion would be to use row_number() which will create an incremented value over each id, then you can use the PIVOT function to return the max(value) for each of these sequenced values:

SELECT ID, [0], [1], [2], [3], [4] 
FROM 
(
  SELECT ID, Value, 
    row_number() over(partition by id 
                        order by id) -1  seq
  FROM yourtable
) SourceTable 
PIVOT 
(
  max(VALUE)
  FOR seq IN ([0], [1], [2], [3], [4])
) AS PivotTable;

See SQL Fiddle with Demo

Upvotes: 3

Related Questions