我想將多行合并為一行，獲取以逗號分隔的值不同的一列。
我寫了下面的查詢，它給出了如下所示的結果。我想要 4 行而不是 9 行，最后一列應該以逗號分隔，如（存盤、廢水、雜項）。
請幫助您的想法，提前致謝！
SELECT DISTINCT
C.CONTRACTID, C.NUMBER, C.STATE,
O.CUSTOMERCODE, O.CUSTOMERNAME,
C.STARTDATE, C.TERMINATIONDATE, CT.Name AS CONTRACTTYPELIST
FROM
[DBO].[CONTRACT] C
INNER JOIN
[ORD].[ORDER] O ON C.CUSTOMERID = O.CUSTOMERID
INNER JOIN
[dbo].[Contract_ContractType] CCT ON CCT.ContractId = C.ContractId
INNER JOIN
[Ref].[ContractType] CT ON CT.ContractTypeId = CCT.ContractTypeId
WHERE
O.ORDERSTATEID = 6
ORDER BY
c.ContractId
我更新了如下所示的查詢，但它在最后一列中給出了長字串，但我只想要一行中該特定記錄 ID 的值。如何糾正？
SELECT distinct
C.CONTRACTID,C.NUMBER, C.STATE ,
O.CUSTOMERCODE,O.CUSTOMERNAME ,
C.STARTDATE , C.TERMINATIONDATE ,
STRING_AGG(CAST(CT.Name AS NVARCHAR(MAX)) , ',') AS CONTRACTTYPELIST
FROM
[DBO].[CONTRACT] C
INNER JOIN
[ORDERING].[ORDER] O ON C.CUSTOMERID = O.CUSTOMERID
INNER JOIN
[dbo].[Contract_ContractType] CCT on CCT.ContractId = C.ContractId
INNER JOIN
[Ref].[ContractType] CT on CT.ContractTypeId = CCT.ContractTypeId
WHERE
O.ORDERSTATEID = 6
GROUP BY
C.CONTRACTID,C.NUMBER, C.STATE ,
O.CUSTOMERCODE,O.CUSTOMERNAME ,
C.STARTDATE , C.TERMINATIONDATE
uj5u.com熱心網友回復：
您想將GROUP BY子句與STRING_AGG函式一起使用
例子：
SELECT STRING_AGG(column_D, ',')
FROM dbo.table
GROUP BY column_A, column_B, column_C
uj5u.com熱心網友回復：
使用 DISTINCT 將查詢包裝在子查詢中。
然后在外部查詢中使用 STRING_AGG。
SELECT
CONTRACTID, [NUMBER], STATE,
CUSTOMERCODE, CUSTOMERNAME,
STARTDATE, TERMINATIONDATE,
STRING_AGG(CONTRACTTYPE,',') AS CONTRACTTYPELIST
FROM
(
SELECT DISTINCT
C.CONTRACTID, C.NUMBER, C.STATE,
O.CUSTOMERCODE, O.CUSTOMERNAME,
C.STARTDATE, C.TERMINATIONDATE,
CT.Name AS CONTRACTTYPE
FROM
[DBO].[CONTRACT] C
JOIN [ORDERING].[ORDER] O
ON C.CUSTOMERID = O.CUSTOMERID
JOIN [dbo].[Contract_ContractType] CCT
ON CCT.ContractId = C.ContractId
JOIN [Ref].[ContractType] CT
ON CT.ContractTypeId = CCT.ContractTypeId
WHERE
O.ORDERSTATEID = 6
) q
GROUP BY
CONTRACTID, [NUMBER], STATE,
CUSTOMERCODE, CUSTOMERNAME,
STARTDATE, TERMINATIONDATE
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/367505.html
標籤：sql sql-server 查询语句
上一篇：SQL互斥條件