2 pivot fields in 1 query

[Former Member]

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

[kothandaraman_nagarajan · Accepted solution]

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

[Former Member · Accepted solution]

Hi Sir,

this is the result of the query, the cost did not show.

[kothandaraman_nagarajan · Accepted solution]

You have to replace my warehouse code with your actual warehouse code.

[Former Member · Accepted solution]

yes, the ALF is one of our warehouse code. i did not replace the others ([REJ_FG],[SFTO-618],[TOOL]), should I?

[kothandaraman_nagarajan · Accepted solution]

Post your modified query here to check.

[Former Member · Accepted solution]

here is the modified query:

SELECT [Item] as Item#,
[ALF],[REJ_FG],[SFTO-618],[TOOL],[qty]
FROM(SELECT T0.[ItemCode] as Item, sum(T0.[TransValue]) AS Cost, T0.[Warehouse] AS WAREHOUSE,sum(T0.[InQty] - T0.[OutQty]) AS QTY
FROM OINM T0
WHERE T0.[DocDate] <= [%0] AND  T0.[ItemCode] = [%1] group by T0.[ItemCode],T0.[Warehouse]) S

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

[kothandaraman_nagarajan · Accepted solution]

No it is not correct. Use my query and just replace my warehouse code with your code.

[Former Member · Accepted solution]

it worked sir! thank you so much!

[kothandaraman_nagarajan]

Hi,

Try this query. Let me know whether it meets your requirement or not:

Note: Replace with correct warehouse code.

SELECT [Item] as Item#,
[OPEN],[REJ_FG],[SFTO-618],[TOOL],[qty]
FROM(SELECT T0.[ItemCode] as Item, sum(T0.[TransValue]) AS Cost, T0.[Warehouse] AS WAREHOUSE,sum(T0.[InQty] - T0.[OutQty]) AS QTY
FROM OINM T0
WHERE T0.[DocDate] <= [%0] AND  T0.[ItemCode] = [%1] group by T0.[ItemCode],T0.[Warehouse]) S

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

Thanks & Regards,

Nagarajan

[Former Member]

Sir Nagarajan,

The query you gave got the idea of what my client needs but the format is different. can we make it like somehow the Cumulative Value is beside the qty.

here is the old query result, they need the Value right next to the Qty.

[kothandaraman_nagarajan]

Yes possible. Let me try and update.