-1

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]

enter image description here

enter image description here

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
shahzad
  • 11
  • 7

1 Answers1

0

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
shahzad
  • 11
  • 7
  • You may want to consider using a [PIVOT](https://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396) statement instead. I'm not sure what sort of performance impact your solution might have over a large number of records, but if it's doing 15+ lookups per record, it might be substantial. – Zac Faragher Nov 27 '17 at 23:29
  • i tried pivot on above warehouse codes but couldn't succeed. so switched to above query. if you can help me, it would be wonderful. – shahzad Nov 27 '17 at 23:35
  • pluse also in results of above query what if i want to add last column which gives me sum of columns used in select statement. i know it would be equal to 'ON Hand' stocks but still i want to know the formula to use in other queries of pivot. – shahzad Nov 27 '17 at 23:38