February 23, 2012 at 9:59 am
hi
i'm trying to create a pivot of sales by sku, month using the following...
SELECTTrnYear, Customer, Name, StockCode, Description,
[Jan], [Feb], [Mar] AS Mar, [Apr] AS Apr, [May] AS May, [Jun] AS Jun,
[Jul] AS Jul, [Aug] AS Aug, [Sep] AS Sep, [Oct] AS Oct, [Nov] AS Nov, [Dec] AS Dec
--INTOdbo.tbl_PROTEUS_dddsales_summary_yoy
FROM
(SELECT TrnYear, TrnMonth, TrnMonthNm, Customer, Name, StockCode, Description, SalesValue
FROMCBF_Actuals_CTE) ps
PIVOT
(
SUM (SalesValue)
FOR TrnMonthNm IN
([Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec])
) AS pvt
the results however are coming up ....
TrnYear, Customer, StockCode, jan, feb, ......
2012......cust1.......stock1.......500....NULL
2012......cust1.......stock1.......NULL...270
so i'm getting new records for each month??
February 23, 2012 at 2:30 pm
PIVOT groups on every field from the inner query that is not the aggregated value or the pivoted column. That includes the TrnMonth. Try removing that from the inner query.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 24, 2012 at 1:12 am
pefrect. that worked fine. thanks
July 11, 2012 at 1:56 am
thought i'd re-open an old thread instead of starting a new one.
I'm trying to create a pivot...
selectInvYear, VectaGrp1Code, VectaGrp1Name, VectaGrp2Code, VectaGrp2Name, VectaGrp3Code, VectaGrp3Name, StkCode, StkDesc, AccNo, AccName,
[Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec]
from(selectInvYear,
VectaGrp1Code, VectaGrp1Name, VectaGrp2Code, VectaGrp2Name, VectaGrp3Code, VectaGrp3Name,
StkCode, StkDesc, AccNo, AccName,
sum(InvVal) InvVal, sum(InvQty) InvQty
fromdbo.tbl_PROTEUS_dddsales_import_02
whereInvDate between '2011-01-01 00:00:00' and '2011-10-31 00:00:00'
and VectaGrp1Code in ('44', '42', '41')
and HomeExport = 'HOME'
and AccNo in ('81209','11154','10020','11176')
group by InvYear, VectaGrp1Code, VectaGrp1Name, VectaGrp2Code, VectaGrp2Name, VectaGrp3Code, VectaGrp3Name,
StkCode, StkDesc, AccNo, AccName) src
PIVOT
(
SUM(InvVal)
FOR InvMthName IN ([Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec])
) AS pvt
...but i'm getting the same issue as above, the values are on different rows for each month. I remove the InvMthName column from the inner query but get the error: Invalid column name 'InvMthName'.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply