Hola a todos, una vez mas recurro a ustedes para que me ayuden con algo, tengo el siguiente query que me obtiene las listas de precios. pero necesito mostrarlas de manera vertical EJE…
Lista1
valor1(lista1)
valor2(lista1)
etc…
Lista2
valor1(lista2)
valor2(lista2)
etc…
Lista3…Etc.
con el query que tengo ahorita muestro las 8 listas pero de manera vertical, si hay un método para poder mostrarlo de manera Horizontal agradecería su ayuda.
Este es mi query—
SELECT DISTINCT T0."SWW" as "Codigo Siberia",T0."ItemCode", T0."ItemName", T1."ItmsGrpNam" as "Categoria",T0."U_SubCategoria",
(Select T10."AvgPrice" FROM OITW T10 Where T0."ItemCode"=T10."ItemCode" And T10."WhsCode"='1000') as "Costo 1000",
(Select T10."OnHand" FROM OITW T10 Where T0."ItemCode"=T10."ItemCode" And T10."WhsCode"='1000') as "Stock 1000",
(Select T10."AvgPrice" FROM OITW T10 Where T0."ItemCode"=T10."ItemCode" And T10."WhsCode"='1001') as "Costo 1001",
(Select T10."OnHand" FROM OITW T10 Where T0."ItemCode"=T10."ItemCode" And T10."WhsCode"='1001') as "Stock 1001",
(Select T10."AvgPrice" FROM OITW T10 Where T0."ItemCode"=T10."ItemCode" And T10."WhsCode"='1002') as "Costo 1002",
(Select T10."OnHand" FROM OITW T10 Where T0."ItemCode"=T10."ItemCode" And T10."WhsCode"='1002') as "Stock 1002",
(Select T10."AvgPrice" FROM OITW T10 Where T0."ItemCode"=T10."ItemCode" And T10."WhsCode"='1005') as "Costo 1005",
(Select T10."OnHand" FROM OITW T10 Where T0."ItemCode"=T10."ItemCode" And T10."WhsCode"='1005') as "Stock 1005",
(Select T10."AvgPrice" FROM OITW T10 Where T0."ItemCode"=T10."ItemCode" And T10."WhsCode"='1006') as "Costo 1006",
(Select T10."OnHand" FROM OITW T10 Where T0."ItemCode"=T10."ItemCode" And T10."WhsCode"='1006') as "Stock 1006",
(Select T10."AvgPrice" FROM OITW T10 Where T0."ItemCode"=T10."ItemCode" And T10."WhsCode"='1007') as "Costo 1007",
(Select T10."OnHand" FROM OITW T10 Where T0."ItemCode"=T10."ItemCode" And T10."WhsCode"='1007') as "Stock 1007",
(Select T10."AvgPrice" FROM OITW T10 Where T0."ItemCode"=T10."ItemCode" And T10."WhsCode"='2000') as "Costo 2000",
(Select T10."OnHand" FROM OITW T10 Where T0."ItemCode"=T10."ItemCode" And T10."WhsCode"='2000') as "Stock 2000",
(select SUM(T10."AvgPrice"*T10."OnHand" ) FROM OITW T10 Where T0."ItemCode"=T10."ItemCode" And T10."WhsCode"='1000' )AS "Costo*Stock 1000",
(select SUM(T10."AvgPrice"*T10."OnHand" ) FROM OITW T10 Where T0."ItemCode"=T10."ItemCode" And T10."WhsCode"='1001' )AS "Costo*Stock 1001",
(select SUM(T10."AvgPrice"*T10."OnHand" ) FROM OITW T10 Where T0."ItemCode"=T10."ItemCode" And T10."WhsCode"='1002' )AS "Costo*Stock 1002",
(select SUM(T10."AvgPrice"*T10."OnHand" ) FROM OITW T10 Where T0."ItemCode"=T10."ItemCode" And T10."WhsCode"='1005' )AS "Costo*Stock 1005",
(select SUM(T10."AvgPrice"*T10."OnHand" ) FROM OITW T10 Where T0."ItemCode"=T10."ItemCode" And T10."WhsCode"='1006' )AS "Costo*Stock 1006",
(select SUM(T10."AvgPrice"*T10."OnHand" ) FROM OITW T10 Where T0."ItemCode"=T10."ItemCode" And T10."WhsCode"='1007' )AS "Costo*Stock 1007",
(select SUM(T10."AvgPrice"*T10."OnHand" ) FROM OITW T10 Where T0."ItemCode"=T10."ItemCode" And T10."WhsCode"='2000' )AS "Costo*Stock 2000",
(select SUM(T10."OnHand" ) FROM OITW T10 Where T0."ItemCode"=T10."ItemCode")AS "Stock Total",
(select SUM(T10."AvgPrice"*T10."OnHand" ) FROM OITW T10 Where T0."ItemCode"=T10."ItemCode")AS "Costo Total",
K10."Costo Promedio",
/*
(Select
Case
When T10."OnHand" = 0 Then 1
When T10."OnHand" <>0 Then T10."OnHand"
End as "Inventario",
(SUM(T10."AvgPrice"*T10."OnHand" ))/ SUM("Inventario")
FROM OITW T10 Where T0."ItemCode"=T10."ItemCode" )AS "Costo Promedio",*/
K0."HNL01",K1."HNL02",K2."HNL03",K3."HNL04",K4."HNLF01",K5."HNLF02",K6."HNLF03",K7."HNLF04"
/,K8."Lista de Precios 9",K9."Lista de Precios 10"/
--,(Select (SUM(T10."AvgPrice"*T10."OnHand" ))/IFNULL(SUM(T10."OnHand" ),1) FROM OITW T10 Where T0."ItemCode"=T10."ItemCode" )AS "Costo Promedio"
--(select T11."Price" FROM ITM1 T11 WHERE T11."ItemCode"=T11."ItemCode" AND T11."PriceList"=1 )
FROM (select (T11."Price")as "HNL01",T11."ItemCode" as "ItemCode"
FROM ITM1 T11
WHERE T11."ItemCode"=T11."ItemCode" AND T11."PriceList"=1
) K0 INNER JOIN
OITM T0 ON T0."ItemCode" = K0."ItemCode" INNER JOIN OITB T1 ON T0."ItmsGrpCod"=T1."ItmsGrpCod"
INNER JOIN
(select (T11."Price")as "HNL02",T11."ItemCode" as "ItemCode"
FROM ITM1 T11
WHERE T11."ItemCode"=T11."ItemCode" AND T11."PriceList"=2
) K1 ON T0."ItemCode" = K1."ItemCode"
INNER JOIN
(select (T11."Price")as "HNL03",T11."ItemCode" as "ItemCode"
FROM ITM1 T11
WHERE T11."ItemCode"=T11."ItemCode" AND T11."PriceList"=3
) K2 ON T0."ItemCode" = K2."ItemCode"
INNER JOIN
(select (T11."Price")as "HNL04",T11."ItemCode" as "ItemCode"
FROM ITM1 T11
WHERE T11."ItemCode"=T11."ItemCode" AND T11."PriceList"=4
) K3 ON T0."ItemCode" = K3."ItemCode"
INNER JOIN
(select (T11."Price")as "HNLF01",T11."ItemCode" as "ItemCode"
FROM ITM1 T11
WHERE T11."ItemCode"=T11."ItemCode" AND T11."PriceList"=5
) K4 ON T0."ItemCode" = K4."ItemCode"
INNER JOIN
(select (T11."Price")as "HNLF02",T11."ItemCode" as "ItemCode"
FROM ITM1 T11
WHERE T11."ItemCode"=T11."ItemCode" AND T11."PriceList"=6
) K5 ON T0."ItemCode" = K5."ItemCode"
INNER JOIN
(select (T11."Price")as "HNLF03",T11."ItemCode" as "ItemCode"
FROM ITM1 T11
WHERE T11."ItemCode"=T11."ItemCode" AND T11."PriceList"=7
) K6 ON T0."ItemCode" = K6."ItemCode"
INNER JOIN
(select (T11."Price")as "HNLF04",T11."ItemCode" as "ItemCode"
FROM ITM1 T11
WHERE T11."ItemCode"=T11."ItemCode" AND T11."PriceList"=8
) K7 ON T0."ItemCode" = K7."ItemCode"
INNER JOIN
(select T11."ItemCode",
Case
When Sum(T11."OnHand")=0 Then 0
When Sum( T11."OnHand") <> 0 Then (SUM(T11."AvgPrice"*T11."OnHand" ))/ SUM(T11."OnHand")
End as "Costo Promedio"
FROM OITW T11 Where T11."ItemCode"=T11."ItemCode" group by T11."ItemCode"
) K10 ON T0."ItemCode" = K10."ItemCode"
/* INNER JOIN
(select (T11."Price")as "Lista de Precios 9",T11."ItemCode" as "ItemCode"
FROM ITM1 T11
WHERE T11."ItemCode"=T11."ItemCode" AND T11."PriceList"=9
) K8 ON T0."ItemCode" = K8."ItemCode"
INNER JOIN
(select (T11."Price")as "Lista de Precios 10",T11."ItemCode" as "ItemCode"
FROM ITM1 T11
WHERE T11."ItemCode"=T11."ItemCode" AND T11."PriceList"=10
) K9 ON T0."ItemCode" = K9."ItemCode"*/