SQL Server Pivot - distinct values

I would like to pivot table a sum of sales in SQL Server.

My table is slightly more complicated than what is below, but this is a good example:

The Pivot I am after should look something like this:

Obviously this is quite straightforward using MS Excel, however, since my data is ridiculously huge, it is housed at a table in SQL Server. Therefore, I am needing write an query to pivot table as example above.

What I have written is:

SELECT DISTINCT [Day] FROM [TABLE]
PIVOT (SUM(Cost) FOR [Prod] IN([Coke], [Pepsi], [Tango])) AS PIVOTSALES


However, I am failing to get the required result. Any advise how to proceed would be greatly appreciated.

2 Answers
2

You're only selecting Day. You need to add the other columns to your SELECT list.

Day

Compare your current query to the solution here.

You are very close, you would only have to add the pivoted fields to your SELECT list (and remove that DISTINCT, because the data is grouped automatically):

SELECT [Day], [Coke], [Pepsi], [Tango]
FROM [TABLE]
PIVOT (SUM([Cost]) FOR [Prod] IN ([Coke], [Pepsi], [Tango])) AS PIVOTSALES


But...

I hope that your table also has a primary key, and if so, this query will not work, since PIVOT not only creates columns for the values of the pivoted field (Prod) that will contain values calculated using the value field (Cost) but also does a GROUP BY by all other columns contained in what is specified in the FROM clause. Therefore, if your table contains more than the mentionned 3 columns Day, Prod and Cost, you will have to specify a subquery in the FROM clause (and give it an alias):

Prod

Cost

GROUP BY

Day

Prod

Cost

SELECT [Day], [Coke], [Pepsi], [Tango]
FROM (SELECT [Day], [Cost], [Prod] FROM [TABLE]) AS [Alias]
PIVOT (SUM([Cost]) FOR [Prod] IN ([Coke], [Pepsi], [Tango])) AS PIVOTSALES


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.