How to transpose rows to columns in SQL Server

Question

I have a fact table as below:

Table Types

TypeId | Name
  1        x
  2        y
  3        z

Table Period:

PeriodId   | Date
 1            2014-01-31
 2            2015-01-31



RowNumber  | Value | TypeId | Identifier | PeriodId
 1            12        1          cc1         1
 2            10        2          cc1         2
 3            17        3          cc1         1
 ..           30       ...         ...         ..
 ..           60        1           cc2        1
              23        2           cc2        2

From these tables I am trying to create a single flatten table as below:

Identifier  |  periodId   |   x  |  y  | z
  cc1              1         12     10   17
  cc1              2          ..    ..   ..
  cc2              1          ..     ..  ..

How can I query to get the data in the above format?

I think you can find some help to your problem here, with [**Dynamic Pivot**](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Radu Gheorghiu, Nov 28 '16 at 15:06

score 0 · Answer 1 · answered Nov 28 '16 at 15:29

INSERT INTO NewTable
SELECT Identifier, PeriodId,
    (SELECT Value FROM [Values] v2 WHERE v2.Identifier = v1.Identifier AND v2.PeriodId = v1.PeriodId AND v2.TypeId = 1) AS 'x',
    (SELECT Value FROM [Values] v2 WHERE v2.Identifier = v1.Identifier AND v2.PeriodId = v1.PeriodId AND v2.TypeId = 2) AS 'y',
    (SELECT Value FROM [Values] v2 WHERE v2.Identifier = v1.Identifier AND v2.PeriodId = v1.PeriodId AND v2.TypeId = 3) AS 'z'
FROM [Values] v1
GROUP BY Identifier, PeriodId

Is this what you need?

How to transpose rows to columns in SQL Server

1 Answers1