Where can i find a guide or examples of how to write a pivot SQL query?

Question

Where can I find a guide or examples of how to write a pivot SQL query?

I am using this temp table as an exercise.

Table Scripts:

CREATE TABLE #PVT (Vendor varchar(20), ConsultantID smallint, Billrate money);

INSERT #PVT VALUES 
('Volt Consulting',1,100), ('Volt Consulting',2,180), 
('Volt Consulting',3,200), ('Volt Consulting',4,120), 
('Volt Consulting',5,240), ('TekSystem Consulting',1,110), 
('TekSystem Consulting',2,120), ('TekSystem Consulting',3,130), 
('TekSystem Consulting',4,220), ('TekSystem Consulting',5,230), 
('Bridge Consulting',1,95), ('Bridge Consulting',2,90), 
('Bridge Consulting',3,104), ('Bridge Consulting',4,103), 
('Bridge Consulting',5,100), ('ChinaSoft Consulting',1,108), 
('ChinaSoft Consulting',2,115), ('ChinaSoft Consulting',3,125), 
('ChinaSoft Consulting',4,130), ('ChinaSoft Consulting',5,120), 
('Excell Data',1,140), ('Excell Data',2,143), 
('Excell Data',3,140), ('Excell Data',4,145), 
('Excell Data',5,200);

SELECT * 
FROM #PVT; 
GO

Thank you in advance :)

your table scripts are good way of asking,here is an answer in SO covering all possibilties of converting rows to columns.Try this and update your question,if you are not getting still..http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server — TheGameiswar, May 17 '16 at 02:01
Also post the required output after trying:check here :https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — TheGameiswar, May 17 '16 at 02:02
http://stackoverflow.com/questions/37215975/how-do-i-separate-query-results-into-columns-where-data-from-each-month-is-repre/37218213#37218213 or Itzik Ben-Gan, T-SQL Fundamentals 2012: http://tsql.solidq.com/books/tsqlfund2012/ — Nikolay Fedorov, May 17 '16 at 07:01

gofr1 · Accepted Answer · 2016-05-18T06:39:12.573

There is nice article on MSDN which explains how to PIVOT or UNPIVOT with examples. Below is an example of PIVOTing your table.

SELECT * 
FROM (
    SELECT  Vendor,
            ConsultantID,
            Billrate
    FROM #PVT
) as t
pivot
(
MAX(Billrate) FOR ConsultantID in ([1],[2],[3],[4],[5])
) as pvt

Output:

Vendor                  1       2       3       4       5
Bridge Consulting       95,00   90,00   104,00  103,00  100,00
ChinaSoft Consulting    108,00  115,00  125,00  130,00  120,00
Excell Data             140,00  143,00  140,00  145,00  200,00
TekSystem Consulting    110,00  120,00  130,00  220,00  230,00
Volt Consulting         100,00  180,00  200,00  120,00  240,00

In this sample we know that there will be 5 column headers. If you have no idea how many there are - you must use dynamic SQL:

DECLARE @columns nvarchar(max),
        @sql nvarchar(max)
--Here we get [1],[2],[3],[4],[5] in the @columns variable to use in dynamic query
SELECT @columns = STUFF(
(SELECT DISTINCT ','+QUOTENAME(ConsultantID)
FROM #PVT
FOR XML PATH ('')),1,1,'')

SET @sql = 
'SELECT * 
FROM (
    SELECT  Vendor,
            ConsultantID,
            Billrate
    FROM #PVT
) as t
pivot
(
MAX(Billrate) FOR ConsultantID in ('+@columns+')
) as pvt'

EXEC (@sql)

Output will be the same.

EDIT

If you want to add totals by Vendor or by Consultants use this (works in SQL Server 2008 and up):

;WITH cte AS (
SELECT *, [1]+[2]+[3]+[4]+[5] as Totals
FROM (
    SELECT  Vendor,
            ConsultantID,
            Billrate
    FROM #PVT
) as t
pivot
(
MAX(Billrate) FOR ConsultantID in ([1],[2],[3],[4],[5])
) as pvt
)

SELECT *
FROM cte
UNION ALL
SELECT 'Total',SUM([1]),SUM([2]),SUM([3]),SUM([4]),SUM([5]), NULL as Totals
FROM cte

For dynamic:

DECLARE @columns nvarchar(max),
        @sql nvarchar(max)

SELECT @columns = STUFF(
(SELECT DISTINCT ','+QUOTENAME(ConsultantID)
FROM #PVT
FOR XML PATH ('')),1,1,'')

SET @sql = 
';WITH cte AS (
SELECT *, ' + REPLACE(@columns,',','+') +' as Totals
FROM (
    SELECT  Vendor,
            ConsultantID,
            Billrate
    FROM #PVT
) as t
pivot
(
MAX(Billrate) FOR ConsultantID in ('+@columns+')
) as pvt
)

SELECT *
FROM cte
UNION ALL
SELECT ''Total'',SUM(' + REPLACE(@columns,',','),SUM(') + '), NULL as Totals
FROM cte
'

EXEC (@sql)

Output:

Vendor                  1       2       3       4       5       Totals
Bridge Consulting       95,00   90,00   104,00  103,00  100,00  492,00
ChinaSoft Consulting    108,00  115,00  125,00  130,00  120,00  598,00
Excell Data             140,00  143,00  140,00  145,00  200,00  768,00
TekSystem Consulting    110,00  120,00  130,00  220,00  230,00  810,00
Volt Consulting         100,00  180,00  200,00  120,00  240,00  840,00
Total                   553,00  648,00  699,00  718,00  890,00  NULL

So then adding another column for instance with the total billrate for each vendor would add SUM (billrate) FOR ConsultantID in () correct? — Suehil, May 17 '16 at 19:49
Hey, I edit my answer to reply on your comment :) Please, check. — gofr1, May 18 '16 at 06:39

Where can i find a guide or examples of how to write a pivot SQL query?

1 Answers1