July 10, 2019 at 6:09 pm
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.
July 10, 2019 at 6:28 pm
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
July 10, 2019 at 6:33 pm
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
July 10, 2019 at 6:41 pm
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
July 10, 2019 at 7:30 pm
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;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 10, 2019 at 7:32 pm
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
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy