Friday, 13 February 2009

SQL Server pivoting data

Example of how to use PIVOT/UNPIVOT keyword in SQL Server 2005 and above.
DECLARE @Attributes TABLE (
id INT,
name VARCHAR(50),
value VARCHAR(50),
dt DATETIME);
DECLARE @AttributesPivoted TABLE (
id INT,
dt DATETIME,
Field1 VARCHAR(50),
Field2 VARCHAR(50)
);
INSERT INTO @Attributes VALUES (1,'Field1', 'A', '20080701');
INSERT INTO @Attributes VALUES (2,'Field1', 'B', '20080701');
INSERT INTO @Attributes VALUES (3,'Field1', 'C', '20080701');
INSERT INTO @Attributes VALUES (3,'Field2', 'X', '20080701');
INSERT INTO @Attributes VALUES (4,'Field1', 'D', '20080702');
INSERT INTO @Attributes VALUES (4,'Field2', 'X', '20080702');
INSERT INTO @Attributes VALUES (5,'Field1', 'E', '20080702');
INSERT INTO @Attributes VALUES (5,'Field2', 'X', '20080702');
SELECT * FROM @Attributes
INSERT INTO @AttributesPivoted
-- Pivot
SELECT id,
dt,
MAX(Field1) AS Field1,
MAX(Field2) AS Field2
FROM @Attributes
PIVOT
(MAX(value) FOR name IN
([Field1], [Field2])) AS P
GROUP BY id, dt;
SELECT * FROM @AttributesPivoted
-- Unpivot
SELECT id, name,value,dt
FROM
(SELECT id, dt, Field1, Field2
 FROM @AttributesPivoted) p
 UNPIVOT
 (value FOR name IN 
	([Field1],[Field2])
) AS unpvt

No comments:

Post a Comment