현재 결과값이 여러줄로 나오게 되는데 H20 부터 H31 까지 컬럼을 합하여 한줄만 나오게 하려면 어떻게 해줘야 할까요
조언 부탁드립니다.
select A.ITEM_CD,
CASE WHEN B.SL_CD = 'H20' THEN GOOD_ON_HAND_QTY ELSE 0 END AS H20,
CASE WHEN B.SL_CD = 'H21' THEN GOOD_ON_HAND_QTY ELSE 0 END AS H21,
CASE WHEN B.SL_CD = 'H22' THEN GOOD_ON_HAND_QTY ELSE 0 END AS H22,
CASE WHEN B.SL_CD = 'H29' THEN GOOD_ON_HAND_QTY ELSE 0 END AS H29,
CASE WHEN B.SL_CD = 'H30' THEN GOOD_ON_HAND_QTY ELSE 0 END AS H30,
CASE WHEN B.SL_CD = 'H31' THEN GOOD_ON_HAND_QTY ELSE 0 END AS H31
FROM I_ONHAND_STOCK A inner join B_STORAGE_LOCATION B on A.sl_cd = B.sl_cd
WHERE B.SL_CD IN ('H20','H21','H22','H29','H30','H31')
AND A.item_cd = N'21-G001-WZ'
GROUP BY A.ITEM_CD,B.SL_CD,A.GOOD_ON_HAND_QTY
UNIERP 쓰시나 보네요..ㅎㅎㅎㅎ
아래와 같이 피벗 활용하시면 쉽게 가능 하십니다..
물론 CASE 구문으로도 가능 하구요
SELECT ITEM_CD, [H20],[H21],[H22],[H29],[H30],[H31]
FROM I_ONHAND_STOCK
PIVOT (
SUM(GOOD_ON_HAND_QTY) FOR SL_CD IN([H20],[H21],[H22],[H29],[H30],[H31])
) AS PVT
WHERE ITEM_CD = N'21-G001-WZ'
GROUP BY ITEM_CD,[H20],[H21],[H22],[H29],[H30],[H31]