Pivot more than one columns

In a recent T-SQL forum post, an interest question was how to pivot two columns in a table.
The raw data is like:
[Time],[Type],[Value],[ID]
N’21:00′,N’A’,60.24,1
N’21:00′,N’B’,20.00,2
N’22:00′,N’C’,21.33,3
N’22:00′,N’D’,80.00,4
And it was required to pivot [Type] and [ID] as following:
Time    A     IDA    B    IDB    C    IDC  D     IDD
21:00  60.24  1    20.00  2     0.00  NULL 0.00  NULL
22:00  0.00   NULL  0.00  NULL  21.33 3    80.00 4
Though since SQL Server 2005 PIVOT is supported, only one column can be pivot.
We need to use the old GROUP BY method in SQL 2000 age to achieve this:
IF object_id(‘tempdb..#Data’) IS NOT NULL
DROP TABLE #Data
CREATE TABLE #Data ([Time] Datetime not null, [Type] nvarchar(10) NOT NULL, [Value] decimal(6,2) NOT NULL, [ID] int not null,PRIMARY KEY CLUSTERED([Time],[ID]))
INSERT #Data([Time],[Type],[Value],[ID])
SELECT N’21:00′,N’A’,60.24,1 UNION ALL
SELECT N’21:00′,N’B’,20.00,2 UNION ALL
SELECT N’22:00′,N’C’,21.33,3 UNION ALL
SELECT N’22:00′,N’D’,80.00,4
SELECT * FROM #Data
DECLARE @Pvt TABLE (PID int identity not null, [ID] int not null, [Type] nvarchar(10) NOT NULL PRIMARY KEY CLUSTERED(PID))
— Get the columns to be pivoted:
INSERT @Pvt ([ID],[Type])
SELECT DISTINCT [ID],[Type]
FROM #Data
DECLARE @PID int,@PIDName nvarchar(40), @ID int,@Type nvarchar(10),@SQL nvarchar(max)
SET @SQL=N’
SELECT CONVERT(nvarchar(50),D.[Time],8) AS [Time]’
SELECT @PID=MIN(PID) FROM @Pvt
WHILE @PID IS NOT NULL
BEGIN
 SELECT @PIDName=N’ID’+[Type],@ID=ID,@Type=[Type] FROM @Pvt WHERE PID=@PID
 — Pivot Type column. CASE statement needs to check all pivoting columns
 SELECT @SQL=@SQL+N’,’+nchar(13)+N’MAX(CASE WHEN D.[ID]=’+CAST(@ID AS nvarchar(30))+N’ AND D.[Type]=”’+@Type+N”’ THEN D.[Value] ELSE 0.00 END) AS [‘+@Type+N’]’
 — Pivot ID column. CASE statement needs to check all pivoting columns
 SELECT @SQL=@SQL+N’,’+nchar(13)+N’MIN(CASE WHEN D.[ID]=’+CAST(@ID AS nvarchar(30))+N’ AND D.[Type]=”’+@Type+N”’ THEN D.[ID] ELSE NULL END) AS [‘+@PIDName+N’]’
 SELECT @PID=MIN(PID) FROM @Pvt WHERE PID>@PID
END
SET @SQL=@SQL+N’
FROM #Data D
GROUP BY D.[Time]’
PRINT @SQL
EXEC(@SQL)
/*
Time    A     IDA    B    IDB    C    IDC  D     IDD
21:00  60.24  1    20.00  2     0.00  NULL 0.00  NULL
22:00  0.00   NULL  0.00  NULL  21.33 3    80.00 4
*/
If you need to pivot more than two columns, Just add the columns in @Pvt and the dynamic SQL.
 
 
Advertisements
This entry was posted in SQL server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s