在NetSuite的SuiteQL中，如何将销售订单关联的ItemFulfillment ID合并为单条CSV记录？

你可以利用NetSuite SuiteQL支持的LISTAGG聚合函数，将多条ItemFulfillment记录的ID和TranID合并成单条CSV格式的结果。以下是具体查询语句：

SELECT 
  ntll.PreviousDoc AS SalesOrderID,
  LISTAGG(t.ID, ', ') WITHIN GROUP (ORDER BY t.ID) AS ItemFulfillmentIDs,
  LISTAGG(t.TranID, ', ') WITHIN GROUP (ORDER BY t.TranID) AS ItemFulfillmentTranIDs
FROM NextTransactionLineLink AS ntll
INNER JOIN Transaction AS t ON t.ID = ntll.NextDoc
WHERE t.type = 'ItemShip' AND ntll.PreviousDoc = 7790
GROUP BY ntll.PreviousDoc

关键说明： #

• LISTAGG(column, ', ')：把指定列的多行值拼接成单个字符串，用逗号加空格作为分隔符（你可以根据需求换成纯逗号','等其他分隔符）
• WITHIN GROUP (ORDER BY t.ID)：指定拼接时的排序规则，确保ID按顺序排列
• GROUP BY ntll.PreviousDoc：按原销售订单ID分组，保证每个销售订单只返回一条合并后的记录

如果只需要ItemFulfillment的ID，不需要TranID，可以简化查询：

SELECT 
  ntll.PreviousDoc AS SalesOrderID,
  LISTAGG(t.ID, ', ') WITHIN GROUP (ORDER BY t.ID) AS ItemFulfillmentIDs
FROM NextTransactionLineLink AS ntll
INNER JOIN Transaction AS t ON t.ID = ntll.NextDoc
WHERE t.type = 'ItemShip' AND ntll.PreviousDoc = 7790
GROUP BY ntll.PreviousDoc

内容的提问来源于stack exchange，提问作者Dario Perez