0

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:

enter image description here

The Pivot I am after should look something like this:

enter image description here

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.

Oday Salim
  • 1,129
  • 3
  • 23
  • 46

2 Answers2

1

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):

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
Wolfgang Kais
  • 4,010
  • 2
  • 10
  • 17
0

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

Compare your current query to the solution here.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52