« Vroom, vrooom…. aka Making Pages Load Faster | Main | ScottGu – Da LINQ Man! »
SQL Server 2005’s Pivot…PIVOT, PIVVOOOOT – Ross Gellar, Friends
By juliana | May 9, 2008
Every time I see SQL Server’s 2005 PIVOT, I think of that episode of Friends where Ross tries to guide his friends as they bring up the couch to his apartment.
But serious, PIVOT is one of those things that I’m still trying to wrap my brain around.
I’m currently digesting this: Pivots with Dynamic Columns in SQL Server 2005
and modified this examples into one script with temporary tables so I can study it better:
DECLARE @Table1 TABLE (ColId INT,ColName VARCHAR(10))
INSERT INTO @Table1 VALUES(1, ‘Country’)
INSERT INTO @Table1 VALUES(2, ‘Month’)
INSERT INTO @Table1 VALUES(3, ‘Day’)
DECLARE @cols NVARCHAR(2000)
SELECT @cols = COALESCE(@cols + ‘,[' + colName + ']‘,
‘[' + colName + ']‘)
FROM @Table1
ORDER BY colName
DECLARE @Table2 TABLE (tID INT,ColID INT,Txt VARCHAR(10))
INSERT INTO @Table2 VALUES (1,1, ‘US’)
INSERT INTO @Table2 VALUES (1,2, ‘July’)
INSERT INTO @Table2 VALUES (1,3, ‘4′)
INSERT INTO @Table2 VALUES (2,1, ‘US’)
INSERT INTO @Table2 VALUES (2,2, ‘Sep’)
INSERT INTO @Table2 VALUES (2,3, ‘11′)
INSERT INTO @Table2 VALUES (3,1, ‘US’)
INSERT INTO @Table2 VALUES (3,2, ‘Dec’)
INSERT INTO @Table2 VALUES (3,3, ‘25′)
SELECT t2.tID
, t1.ColName
, t2.Txt
FROM @Table1 AS t1
JOIN @Table2
AS t2 ON t1.ColId = t2.ColID
SELECT tID
, [Country]
, [Day]
, [Month]
FROM ( SELECT t2.tID
, t1.ColName
, t2.Txt
FROM @Table1 AS t1
JOIN @Table2
AS t2 ON t1.ColId = t2.ColID
) p PIVOT ( MAX([Txt])
FOR ColName IN ( [Country], [Day],
[Month] ) ) AS pvt
ORDER BY tID ;
Topics: I Canz Code! | No Comments »
Use the Highlighter