Below mentioned query produces attached results but I want to translate stocks on warehouse code wise in horizontal layout:
SELECT T0.[ItemCode],
T0.[ItemName],
T1.[OnHand],
T1.[WhsCode]
FROM
OITM T0 INNER JOIN OITW T1 ON T0.[ItemCode] = T1.[ItemCode]
Advertisement
Answer
Here is the Answer to my own question.
SELECT T0.ItemCode, T0.ItemName, T1.ItmsGrpNam, T0.[OnHand] as 'Total in Stock', T0.[IsCommited] as 'Committed', T0.[OnOrder] as 'Ordered', ((T0.[OnHand] - T0.[IsCommited]) + T0.OnOrder) AS 'Available', (SELECT OnHand FROM OITW WHERE ItemCode = T0.ItemCode AND WhsCode Like '100') AS '100', (SELECT OnHand FROM OITW WHERE ItemCode = T0.ItemCode AND WhsCode Like '101') AS '101', (SELECT OnHand FROM OITW WHERE ItemCode = T0.ItemCode AND WhsCode Like '122') AS '122', (SELECT OnHand FROM OITW WHERE ItemCode = T0.ItemCode AND WhsCode Like '133') AS '133', (SELECT OnHand FROM OITW WHERE ItemCode = T0.ItemCode AND WhsCode Like '144') AS '144', (SELECT OnHand FROM OITW WHERE ItemCode = T0.ItemCode AND WhsCode Like '150') AS '150', (SELECT OnHand FROM OITW WHERE ItemCode = T0.ItemCode AND WhsCode Like '151') AS '151', (SELECT OnHand FROM OITW WHERE ItemCode = T0.ItemCode AND WhsCode Like '160') AS '160', (SELECT OnHand FROM OITW WHERE ItemCode = T0.ItemCode AND WhsCode Like '161') AS '161', (SELECT OnHand FROM OITW WHERE ItemCode = T0.ItemCode AND WhsCode Like '177') AS '177', (SELECT OnHand FROM OITW WHERE ItemCode = T0.ItemCode AND WhsCode Like '188') AS '188', (SELECT OnHand FROM OITW WHERE ItemCode = T0.ItemCode AND WhsCode Like '190') AS '190', (SELECT OnHand FROM OITW WHERE ItemCode = T0.ItemCode AND WhsCode Like '198') AS '198', (SELECT OnHand FROM OITW WHERE ItemCode = T0.ItemCode AND WhsCode Like '222') AS '222', (SELECT OnHand FROM OITW WHERE ItemCode = T0.ItemCode AND WhsCode Like '333') AS '333', (SELECT OnHand FROM OITW WHERE ItemCode = T0.ItemCode AND WhsCode Like '444') AS '444', (SELECT OnHand FROM OITW WHERE ItemCode = T0.ItemCode AND WhsCode Like '555') AS '555', (SELECT OnHand FROM OITW WHERE ItemCode = T0.ItemCode AND WhsCode Like '999') AS '999' FROM DBO.OITM T0 INNER JOIN DBO.OITB T1 ON T1.ItmsGrpCod = T0.ItmsGrpCod Where T0.Onhand > 0 ORDER BY T0.ItemCode