英文:
Concat SQL row into comma separated list
问题
I'm having an issue trying to Concat a comma separator list within SQL when the data matches. I have a table with 6 columns. I want to check if the LicenceNumbers match and then concatenate the ItemsNumbers into a comma separator list if the LicenceNumbers are the same.
Here is the query I've, but it combines the return result as 2 but adds all licences to each row:
SELECT DISTINCT
w2.Order,
w2.Customer,
w2.Contract,
w2.Licence,
w2.CSSNTName,
CASE
WHEN w2.Licence= w1.LicenceTHEN
(SELECT '[' + STRING_AGG(ISNULL(Item, ' '), ',') FROM #WorkingTable2) + ']';
ELSE '[' + w2.Item+ ']';
END AS Item
FROM
#WorkingTable2 w2
INNER JOIN #WorkingTable2 w1 ON w1.Licence= w2.Licence
Table:
| Order || Customer || Contract || Licence || Item || CSSNT |
| ----- || -------- || -------- || ------- || ---- || ----- |
| 5762 || 5273 || 70703US || 420D3 || 8.10 || ABC02 |
| 5762 || 5273 || 70703US || 420D3 || 8.9 || ABC02 |
| 1234 || 1111 || 123US || 1234 || 9.10 || ABC01 |
What Results I'm looking for:
| Order || Customer || Contract || Licence || Item || CSSNT |
| ----- || -------- || -------- || ------- || --------- || ----- |
| 5762 || 5273 || 70703US || 420D3 || [8.9,8.10]|| ABC02 |
| 1234 || 1111 || 123US || 1234 || [9.10] || ABC01 |
英文:
I'm having an issue trying to Concat a comma separator list within SQL when the data matches. I have a table with 6 columns. I want to check if the LicenceNumbers match and then conconcatenate the ItemsNumbers into a comma separator list if the LicenceNumbers are the same.
Here is the query I've, but its combines the return result as 2 but adds all licences to each row
SELECT DISTINCT
w2.Order,
w2.Customer,
w2.Contract,
w2.Licence,
w2.CSSNTName,
CASE
WHEN w2.Licence= w1.LicenceTHEN
(SELECT '[' + STRING_AGG(ISNULL(Item, ' '), ',') FROM #WorkingTable2) + ']'
ELSE '[' + w2.Item+ ']'
END AS Item
FROM
#WorkingTable2 w2
INNER JOIN #WorkingTable2 w1 ON w1.Licence= w2.Licence
Table
| Order || Customer || Contract || Licence || Item || CSSNT |
| ----- || -------- || -------- || ------- || ---- || ----- |
| 5762 || 5273 || 70703US || 420D3 || 8.10 || ABC02 |
| 5762 || 5273 || 70703US || 420D3 || 8.9 || ABC02 |
| 1234 || 1111 || 123US || 1234 || 9.10 || ABC01 |
What Results I'm looking for
| Order || Customer || Contract || Licence || Item || CSSNT |
| ----- || -------- || -------- || ------- || --------- || ----- |
| 5762 || 5273 || 70703US || 420D3 || [8.9,8.10]|| ABC02 |
| 1234 || 1111 || 123US || 1234 || [9.10] || ABC01 |
答案1
得分: 0
让我知道这是否有效:
SELECT
w2.Order,
w2.Customer,
w2.Contract,
w2.Licence,
w2.CSSNTName,
CASE
WHEN w2.Licence = w1.Licence THEN
(SELECT STRING_AGG(Item, ',') FROM #WorkingTable2 WHERE Licence = w2.Licence)
ELSE
w2.Item
END AS Item
FROM
#WorkingTable2 w2
INNER JOIN #WorkingTable2 w1 ON w1.Licence = w2.Licence
英文:
Let me know if this works
SELECT
w2.Order,
w2.Customer,
w2.Contract,
w2.Licence,
w2.CSSNTName,
CASE
WHEN w2.Licence = w1.Licence THEN
(SELECT STRING_AGG(Item, ',') FROM #WorkingTable2 WHERE Licence = w2.Licence)
ELSE
w2.Item
END AS Item
FROM
#WorkingTable2 w2
INNER JOIN #WorkingTable2 w1 ON w1.Licence = w2.Licence
答案2
得分: 0
你可以使用一个简单的聚合/分组方法。
示例
Declare @YourTable Table ([Order] varchar(50),[Customer] varchar(50),[Contract] varchar(50),[Licence] varchar(50),[Item] varchar(50),[CSSNT] varchar(50))
Insert Into @YourTable Values
(5762,5273,'70703US','420D3',8.10,'ABC02')
,(5762,5273,'70703US','420D3',8.9,'ABC02')
,(1234,1111,'123US','1234',9.10,'ABC01')
Select [Order]
,[Customer]
,[Contract]
,[Licence]
,[Item] = concat('[',string_agg(item,', '),']')
,[CSSNT]
From @YourTable
Group By [Order]
,[Customer]
,[Contract]
,[Licence]
,[CSSNT]
结果
英文:
You can use a simple aggregate/group by approach
Example
Declare @YourTable Table ([Order] varchar(50),[Customer] varchar(50),[Contract] varchar(50),[Licence] varchar(50),[Item] varchar(50),[CSSNT] varchar(50))
Insert Into @YourTable Values
(5762,5273,'70703US','420D3',8.10,'ABC02')
,(5762,5273,'70703US','420D3',8.9,'ABC02')
,(1234,1111,'123US','1234',9.10,'ABC01')
Select [Order]
,[Customer]
,[Contract]
,[Licence]
,[Item] = concat('[',string_agg(item,','),']')
,[CSSNT]
From @YourTable
Group By [Order]
,[Customer]
,[Contract]
,[Licence]
,[CSSNT]
Results
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论