Why Is This SQL Server Pivot Query Not Working?

Question

Why Is This SQL Server Pivot Query Not Working?

chris-650026

SSC Enthusiast

Points: 134
More actions
July 10, 2019 at 6:09 pm

#3661681
I'm using the following SQL query to return a table with 4 columns Year, Month, Quantity Sold, Stock_Code,
```
SELECT yr, mon, sum(Quantity) as Quantity, STOCK_CODE
FROM [All Stock Purchased]
group by yr, mon, stock_code
order by yr, mon, stock_code
```
This is an example of some of the data BUT I have about 3000 Stock_Codes and approx 40 yr/mon combinations. Thus the full table has approx 20,000 rows!
I want to pivot this into a table which has a row for each SKU and columns for every Year/Month combination.
I have never used Pivot before so have done some research and have created a SQL query that I believe should work.
```
select * from
(SELECT yr, 
mon, Quantity,
STOCK_CODE
FROM           [All Stock Purchased]) AS BaseData
pivot (
sum(Quantity)
For Stock_Code
in ([4 2015]
,[5 2015]
,[6 2015]
,[7 2015]........
)
) as PivotTable
```
This query returns the following table:
Whereas I want col1 to be Stock_Code and col2 to show the quantity of that stock code sold in 4 2015.
Would really like to understand what is wrong with my code above please.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

jonathan.crawford SSCertifiable Points: 6610 More actions · Answer 1

Your stock code is not [4 2015], that's the month and year. it's working, it's just that you aren't asking for what you want.

select * from
(SELECT mon + ' ' + yr AS [YrMth],
Quantity,
STOCK_CODE
FROM           [All Stock Purchased]) AS BaseData
pivot (
sum(Quantity)
For [YrMth]
in ([4 2015]
,[5 2015]
,[6 2015]
,[7 2015]........
)
) as PivotTable

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

chris-650026 SSC Enthusiast Points: 134 More actions · Answer 2

Thanks for the reply. The query is working in that the 4 2015, 5 2015 are column headers. But, it isnt working in that columns 1 and 2 of the table are also showing 2015, 5 etc when I want a single column which shows stock code. Thus the table would be:

stock code, quantity sold in 4 2015, quantity sold in 5 2015 etc etc

jonathan.crawford SSCertifiable Points: 6610 More actions · Answer 3

right, but the "stockCode IN (...list...)" part has to be matched by finding codes that actually exist. You don't have stockCode [4 2015] in your data, and you really wanted to have stock code for every row. Try the SQL I copied in and see if it works. I'm not sure the grouping will be right, but might be enough to get you on your way.

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

Thom A SSC Guru Points: 99126 More actions · Answer 4

I personally prefer a Cross Tab. I can't test this as your sample data is an image, which is impossible to use, but I would personally do something like this:

SELECT ASP.Yr,
       ASP.Mon,
       SUM(CASE ASP.STOCKCODE WHEN 100105 THEN ASP.Quantity END) AS [100105],
       SUM(CASE ASP.STOCKCODE WHEN 100135 THEN ASP.Quantity END) AS [100135],
       SUM(CASE ASP.STOCKCODE WHEN 100237 THEN ASP.Quantity END) AS [100237] --You get the idea
FROM [All Stock Purchased] ASP --Ideally you should avoid special characters, and names that need to be delimit identified
GROUP BY ASP.Yr,
         ASP.Mon;

If you want your pivot to group on STOCKCODE, then you would do the below:

SELECT ASP.STOCKCODE,
       SUM(CASE WHEN ASP.Yr = 2015 AND ASP.Mon = 4 THEN ASP.Quantity END) AS [4 2015],
       SUM(CASE WHEN ASP.Yr = 2015 AND ASP.Mon = 5 THEN ASP.Quantity END) AS [5 2015],
       SUM(CASE WHEN ASP.Yr = 2015 AND ASP.Mon = 6 THEN ASP.Quantity END) AS [6 2015] --You get the idea
FROM [All Stock Purchased] ASP --Ideally you should avoid special characters, and names that need to be delimit identified
GROUP BY ASP.STOCKCODE;

This reply was modified 4 years, 9 months ago by Thom A.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

drew.allen SSC Guru Points: 76944 More actions · Answer 5

PIVOTs are not very flexible. I almost always use a CROSSTAB over a PIVOT, because CROSSTABs are so much more flexible (and the syntax makes a lot more sense).

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA