Pivot Two Columns with T-SQL(Sample Code)


You can use PIVOT function whcih came with SQL Server 2005. If you want to PIVOT result with multiple columns, one option is to PIVOT one column at a time and JOIN the result together. There is another way to do it to PIVOT multiple column in a single query. You need to create another anchor column alias and values for pivoting.
Here is an example:
DECLARE @t TABLE (
KmBands VARCHAR(50),
MonthDiff INT,
TotalProRataDue DECIMAL(8, 2),
Cases INT)

INSERT INTO @t
VALUES (‘Less then 2083’,-2,26260.95,33 ),
(‘Between 2083-3000’,-2,518.67,16 ),
(‘More then 3000’,-2,5870.2,182),
(‘Less then 2083’,-1,5807.79,174 ),
(‘Between 2083-3000’,-1,426.46,32 ),
(‘More then 3000’,-1,1064.53,37)

SELECT KmBands, MAX([-1]) AS [1MonthArear],MAX([999]) AS [1MonthCases], MAX([-2]) AS [2MonthArear],MAX([998]) AS [2MonthCases]
FROM
(SELECT KmBands, MonthDiff, MonthDiff+1000 AS MonthDiff2,TotalProRataDue, Cases FROM @t)
src
PIVOT (MAX(TotalProRataDue) FOR MonthDiff IN ([-1],[-2])) pvt1
PIVOT (max(Cases) FOR MonthDiff2 IN ([999],[998])) pvt2
GROUP BY KmBands
ORDER BY
CASE WHEN KmBands LIKE ‘%2083’ THEN 1 WHEN KmBands LIKE ‘%2083-3000’THEN 2 WHEN KmBands LIKE ‘%3000’ THEN 3 END

The original question is from this thread at MSDN T-SQL forum:
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/cc6483cb-646b-4a7c-b852-7c1f268c9952

Advertisements


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