pricelist column in one query

[former_member377478]

Hello,

please, how can I add into a query bellow asked pricelists to see them in columns not in rows?

SELECT T0.[ItemCode], T0.[ItemName], T0.[CardCode], T0.[SuppCatNum], T1.[Price], T1.[Currency], T0.[AvgPrice], T1.[PriceList], T4.[ListName], T2.[ItmsGrpNam], T0.[UpdateDate], T3.[CardName] FROM OITM T0 INNER JOIN ITM1 T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON T0.ItmsGrpCod = T2.ItmsGrpCod INNER JOIN OCRD T3 ON T0.CardCode = T3.CardCode INNER JOIN OPLN T4 ON T1.PriceList = T4.ListNum WHERE T4.[ListName]='[%0]' OR T4.[ListName]='[%1]' AND T1.[Price] > 0

Thank you in advance for your help,

rgds jonmar7

[former_member204969 · Accepted solution]

Try this:

SELECT T0.ItemCode, T0.ItemName, T0.CardCode, T0.SuppCatNum, T0.AvgPrice, 
 T1.Price, T1.Currency'cur',T1.PriceList, T4.ListName,
 T5.Price, T5.Currency'cur',T5.PriceList, T6.ListName,
 T2.ItmsGrpNam, T0.UpdateDate, T3.CardName
FROM OITM T0
 INNER JOIN ITM1 T1 ON T0.ItemCode = T1.ItemCode
 INNER JOIN OPLN T4 ON T1.PriceList = T4.ListNum
 INNER JOIN ITM1 T5 ON T0.ItemCode = T5.ItemCode
 INNER JOIN OPLN T6 ON T5.PriceList = T6.ListNum
 INNER JOIN OITB T2 ON T0.ItmsGrpCod = T2.ItmsGrpCod
 INNER JOIN OCRD T3 ON T0.CardCode = T3.CardCode
 WHERE T4.ListName='[%0]' and T6.ListName='[%1]'
   AND (T1.Price >0 or T5.Price >0)

[former_member377478]

Hello,

it is what I wanted... Thank you