Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

2 pivot fields in 1 query

Dear Experts,

i have managed to learn how to use the pivot in queries. the original query was to list all qty in each warehouse in a pivot table (http://scn.sap.com/thread/3703055).  Now I want to add the cumulative value beside the warehouse. the format should be like this

Item Code 'WHSE1' 'Cumulative Value of WHSE1' 'WHSE 2' 'Cumulative Value of WHSE2'

itm001          50                 1500                             50                         1200

below is my query:

SELECT [Code] as Code, [Item] as Name,

[ABU],

[ABUC],

[ALF],

[ALFC],

[AUR],

[AURC],

[BAG],

[BAGC],

[BAM],

[BAMC],

[BAR],

[BARC],

[BAT],

[BATC],

[BIC],

[BICC],

[CAL],

[CALC],

[CAM],

[CAMC],

[CAU],

[CAUC],

[DUB],

[DUBC],

[HER],

[HERC],

[ILC],

[ILCC],

[MIN],

[MINC],

[OFF],

[OFFC],

[PNG],

[PNGC],

[PTB],

[PTBC],

[ROX],

[ROXC],

[SW1],

[SW1C],

[SW2],

[SW2C],

[SW3],

[SW3C],

[SW4],

[SW4C],

[TAL],

[TALC],

[TU2],

[TU2C],

[TU1],

[TU1C]

FROM(SELECT T1.[ItemCode] as Code, T1.[ItemName] as Item, sum(T0.[InQty] - T0.[OutQty]) AS QTY,sum(tO.tRANSVALUE)as Cost, T0.[Warehouse] AS WAREHOUSE

FROM OINM T0  INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode

WHERE T0.[DocDate] <= [%0] AND t1.itemcode BETWEEN [%1] and [%2] group by T1.[ItemCode], T1.[ItemName],T0.[Warehouse]) A

PIVOT

(SUM(QTY) FOR WAREHOUSE IN ([ABU],[ALF],[AUR],[BAG],[BAM],[BAR],[BAT],[BIC],[CAL],[CAM],[CAU],[DUB],[HER],[ILC],[MIN],[OFF],[PNG],[PTB],[ROX],[SW1],[SW2],[SW3],[SW4],[TAL],[TU2],[TU1])) P

FROM(SELECT T1.[ItemCode] as Code, T1.[ItemName] as Item,sum(T0.[TransValue]) as Cost

FROM OINM T0  INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode

WHERE T0.[DocDate] <= [%0] AND t1.itemcode BETWEEN [%1] and [%2] group by T1.[ItemCode], T1.[ItemName],T0.[TransValue]) B

(SUM(Cost) FOR WAREHOUSE IN ([ABUC],[ALFC],[AURC],[BAGC],[BAMC],[BARC],[BATC],[BICC],[CALC],[CAMC],[CAUC],[DUBC],[HERC],[ILCC],[MINC],[OFFC],[PNGC],[PTBC],[ROXC],[SW1C],[SW2C],[SW3C],[SW4C],[TALC],[TU2C],[TU1C])) P

Former Member
replied

Try this query:

SELECT [Item] as Item#,

[OPEN],Opencost,[REJ_FG],REJ_FGcost,[SFTO-618],SFTO618cost,[TOOL],TOOLcost


FROM(SELECT T0.[ItemCode] as Item, sum(T0.[InQty] - T0.[OutQty]) AS QTY, T0.[Warehouse] AS WAREHOUSE,

Case when T0.[Warehouse] = 'open' then sum(T0.[TransValue]) end as 'Opencost',
Case when T0.[Warehouse] = 'REJ_FG' then sum(T0.[TransValue]) end as 'REJ_FGcost',
Case when T0.[Warehouse] = 'SFTO-618' then sum(T0.[TransValue]) end as 'SFTO618cost',
Case when T0.[Warehouse] = 'TOOL' then sum(T0.[TransValue]) end as 'TOOLcost'


FROM OINM T0

WHERE T0.[DocDate] <= [%0] AND  T0.[ItemCode] = [%1] group by T0.[ItemCode],T0.[Warehouse]) S

PIVOT
(SUM(QTY) FOR WAREHOUSE IN ([OPEN],[REJ_FG],[SFTO-618],[TOOL])) P

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question