Concat SQL行为逗号分隔的列表

huangapple go评论72阅读模式
英文:

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]

结果

Concat SQL行为逗号分隔的列表

英文:

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

Concat SQL行为逗号分隔的列表

huangapple
  • 本文由 发表于 2023年4月1日 00:24:33
  • 转载请务必保留本文链接:https://go.coder-hub.com/75900756.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定