Convert Rows to columns with Pivot in SQL Server

Question

I have following table with data:

date            Plant          Weight
2017-11-11  Gent        26
2017-11-11  Ang         25
2017-11-11  Ger         24
2017-11-11  ISS         23
2017-11-10  Gent        26
2017-11-10  Ang         25
2017-11-11  Ger         24
2017-11-11  ISS         23
2017-11-11  Gent        12
2017-11-11  Ang         13
2017-11-10  Gent        100

I would like it to come out as a pivot table, like this:

datum           Ang Gent  Ger  ISS
2017-11-10      25  126   0    0
2017-11-11      38   38  48   46

Thanks for the help.

Have you tried a query yet. Google for "SQL pivot query," and you will find tons of resources which can help you. — Tim Biegeleisen, Nov 19 '17 at 10:50
Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Imran Ali Khan, Nov 22 '17 at 09:17

score 0 · Answer 1 · answered Nov 19 '17 at 10:54

0

Try this:

SELECT *
FROM myTable
PIVOT
(
   MAX(Weight) FOR [Plant] IN ([Ang], [Gent], [Ger], [ISS])
) PVT;

answered Nov 19 '17 at 10:54

gotqn

42,737
46
157
243

Yogesh Sharma · Answer 2 · 2017-11-20T13:08:29.953

First, you could use of Conditional case expression condition if you don't want to go with Pivot operator

SELECT DATE [datum],
          SUM(CASE(PLANT) WHEN 'Ang' THEN Weight ELSE NULL END) [Ang],
          SUM(CASE(PLANT) WHEN 'Gent' THEN Weight ELSE NULL END) [Gent],
          SUM(CASE(PLANT) WHEN 'Ger' THEN Weight ELSE 0 END) [Ger],
          SUM(CASE(PLANT) WHEN 'ISS' THEN Weight ELSE 0 END) [ISS]
FROM <table> GROUP BY [DATE]

Other one is dynamic pivot way if you are not satisfied with conditional case expression

DECLARE @Col NVARCHAR(MAX)
DECLARE @Query NVARCHAR(MAX)

SET @Col = STUFF((SELECT DISTINCT ','+QUOTENAME(PLANT) FROM <table> FOR XML PATH('')),1,1, '')

SET @Query = N'SELECT * FROM <table> 
                    PIVOT
                    (
                       sum(Weight) FOR [Plant] IN ('+@Col+')
                    ) PVT'

execute sp_executesql @Query

Result :

datum           Ang Gent  Ger  ISS
2017-11-10      25  126   0    0
2017-11-11      38   38  48   46

The 'non-dynamic conditional case expression' solves my problem well but how can you create totals row- and columnwise? — Danny G, Nov 23 '17 at 18:42

Danny G · Answer 3 · 2017-11-25T08:11:28.980

select [datum],[Gent],[Ang],[ISS],[Ger],[Gent]+[Angouleme]+[ISS]+[Gerona] as [TOTAAL] FROM (select plant,datum,sum(weight) as weight FROM [MEPDARPD].[dbo].[Table1]  group by plant,datum)t
PIVOT
(
sum(Weight) 
FOR [Plant] IN ([Gent],[Ang],[ISS],[Ger])
) AS p

Below the query with totals in rows and columns

select datum,
sum(case(plant) WHEN 'Ang' then weight else 0 END) as [Ang],
sum(case(plant) WHEN 'Gent' then weight else 0 END) as [Gent],
sum(case(plant) WHEN 'ISS' then weight else 0 END) as [ISS],
sum(case(plant) WHEN 'Ger' then weight else 0 END) as [Ger],
SUM(weight) as total
from MEPDARPD.dbo.rous group by datum
with rollup

That query solves my problem. Thanks for the help.

Convert Rows to columns with Pivot in SQL Server

3 Answers3