pivot in oinm table

[Former Member]

Dear experts,

I am studying the pivot clause to make reports horizontally, please help me troubleshoot the query below a horizontal inventory reprot, i do not seem to understand the S and P sections,

SELECT P.[Warehouse],

[1] as 'ABU',

[2] as 'ALF',

[3] as 'AUR',

[4] as 'BAG',

[5] as 'BAM',

[6] as 'BAR',

[7] as 'BAT',

[8] as 'BEN',

[9] as 'BIC',

[10] as 'CAL',

[11] as 'CAM',

[12] as 'CAU',

[13] as 'DUB',

[14] as 'DV1',

[15] as 'DV2',

[16] as 'FIN',

[17] as 'HER',

[18] as 'ILC',

[19] as 'MIN',

[20] as 'OFF',

[21] as 'PNG',

[22] as 'PTB',

[23] as 'ROX',

[24] as 'SLA',

[25] as 'SW1',

[26] as 'SW2',

[27] as 'SW3',

[28] as 'SW4',

[29] as 'TAL',

[30] as 'TU1',

[31] as 'TU2',

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

FROM OINM T0

WHERE T0.[DocDate] <= [%0] AND  T0.[ItemCode] = [%1] ) S

PIVOT ([QTY] FOR [WAREHOUSE] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])) P

the query should return a format like this:

Item Code   BAT BAM SW1

ITM001         X      X      X

ITM002         X      X      X

ITM003         X      X      X

for your advices please

thanks!

Carlo

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this query. Same way add all other warehouse.

SELECT [Item] as Item#,

[ABU],

[ALF] ,

[AUR] ,

[BAG]

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

FROM OINM T0

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

PIVOT
(SUM(QTY) FOR WAREHOUSE IN ([ABU],[ALF],[AUR],[BAG])) P

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

HI sir Ngarajan,

the query worked, can u explain to me what errors did i made in my query, still can not get the general idea of using pivot.

[kothandaraman_nagarajan · Accepted solution]

Hi,

Problem 1 :-  P.[Warehouse]

Since already adding warehouses in header, then P.warehouse is not valid

Problem 2:- [1] as 'ABU'

There is [1] warehouse in your DB. So you should not add in header as well as in pivot

Problem 3-:sum(T0.[InQty] - T0.[OutQty])

When we use sum function, all other fields should be grouped

Problem 4:- [QTY]

Pivot function needs one of the aggregate function like SUM, Count, Max, Min. So you need to SUM function

Hope you understand above problems.

Thanks & Regards,

Nagarajan