sql - 将多行分组为一行

Question

这是我根据 ITEM SID 读取存储数量的脚本，我需要将与相同 ITEM SID 相关的所有记录分组到一行中，在下面找到结果和预期结果的示例图像。

SELECT DISTINCT
i.SBS_NO,
to_char(i.ITEM_SID) as ITEMSID,
i.ALU ,
a.LOT_NUMBER ,
a.LOT_NAME,
a.LOT_NOTE,
a.EXPIRY_DATE,
a.ACTIVE ,
--s.STORE_NO,
--s.QTY,
DECODE(S.STORE_NO,1,S.QTY,'0') STR1QTY,
DECODE(S.STORE_NO,2,S.QTY,'0') STR2QTY,
DECODE(S.STORE_NO,3,S.QTY,'0') STR3QTY,
DECODE(S.STORE_NO,4,S.QTY,'0') STR4QTY,
DECODE(S.STORE_NO,5,S.QTY,'0') STR5QTY,
DECODE(S.STORE_NO,6,S.QTY,'0') STR6QTY,
DECODE(S.STORE_NO,7,S.QTY,'0') STR7QTY,
DECODE(S.STORE_NO,0,S.QTY,'0') STR0QTY,
DECODE(S.STORE_NO,99,S.QTY,'0') WHQTY,

i.DESCRIPTION2,
i.DESCRIPTION3,
i.DESCRIPTION4,
i.DCS_CODE,
i.ATTR,
i.SIZ,
i.FST_RCVD_DATE,
i.UDF1_DATE,
i.QTY_PER_CASE,
i.ACTIVE,
i.MARKDOWN_PRICE,
i.UDF2_VALUE as ITEM_STATUS

from inventory_v i 
inner join  LOT a 
on i.ITEM_SID = a.ITEM_SID

inner join LOT_QTY s
on a.ITEM_SID=s.ITEM_SID

where i.sbs_no=1
and i.ITEM_SID=a.ITEM_SID
--and i. ALU='358N690175'
 and a.ITEM_SID=s.ITEM_SID
 and i.SBS_NO=a.SBS_NO
 and a.SBS_NO=s.SBS_NO
 and s.STORE_No in (0,1,2,3,4,5,6,7,99)
 and a.ACTIVE=1
 and i.ACTIVE=1

GROUP BY 
 i.SBS_NO,
 i.ITEM_SID,
 i.ALU ,
 a.LOT_NUMBER ,
 a.LOT_NAME,
 a.LOT_NOTE,
 a.EXPIRY_DATE,
 a.ACTIVE ,
 s.STORE_NO,
 s.QTY,
 i.DESCRIPTION2,
 i.DESCRIPTION3,
 i.DESCRIPTION4,
 i.DCS_CODE,
 i.ATTR,
 i.SIZ,
 i.FST_RCVD_DATE,
 i.UDF1_DATE,
 i.QTY_PER_CASE,
 i.ACTIVE,
 i.MARKDOWN_PRICE,
 i.UDF2_VALUE
 ;

---

谢谢

---

[这里是当前结果]

[预期结果]

标签： sqlqlikviewqliksense