英文:
SQL Split, Join & Merge
问题
TableDO
| DOID | TranID |
| -------- | ------ |
| 1 | 1 2 3 |
| 2 | 2 4 |
TblTransporter
| TranID |Transporter |
| -------- | -------- |
| 1 | ABC Tran |
| 2 | BBC Tran |
| 3 | CBC Tran |
| 4 | DBC Tran |
需要的结果来自TableDO
| DOID | Transporter |
| -------- | -------- |
| 1 | ABC Tran,BBC Tran,CBC Tran|
| 2 | BBC Tran,DBC Tran |
我理解您不想使用String_Split函数，以下是一种不使用该函数的查询方法：
SELECT d.DOID,
(SELECT STRING_AGG(t.Transporter, ',')
FROM TblTransporter t
WHERE CHARINDEX(' ' + CAST(t.TranID AS NVARCHAR(MAX)) + ' ', ' ' + d.TranID + ' ') > 0
) AS Transporter
FROM TableDO d
请注意，这个查询假定TranID是一个以空格分隔的字符串，可以与TblTransporter中的TranID匹配。
英文:
TableDO
| DOID | TranID |
| -------- | ------ |
| 1 | 1 2 3 |
| 2 | 2 4 |
TblTransporter
| TranID |Transporter |
| -------- | -------- |
| 1 | ABC Tran |
| 2 | BBC Tran |
| 3 | CBC Tran |
| 4 | DBC Tran |
Result require From TableDO
| DOID | Transporter |
| -------- | -------- |
| 1 | ABC Tran,BBC Tran,CBC Tran|
| 2 | BBC Tran,DBC Tran |
I have tried
Select o.DoNo,t.Transporter as tpt
From DO o
outer apply String_Split(o.Transporter,' ') s
left join Transporter as t on t.TID = s.value
Which Shows Result
| DONO | Tpt |
| -------- | -------- |
| 1 | ABC Tran |
| 1 | BBC Tran |
| 1 | CBC Tran |
| 2 | BBC Tran |
| 2 | DBC Tran |
I do not want to use String_Split Function as it requre Database Compatibility.
答案1
得分: 1
你可以尝试使用 STRING_AGG()
Select o.DoNo,STRING_AGG(t.Transporter,',') as tpt
From DO o
outer apply String_Split(o.Transporter,' ') s
left join Transporter as t on t.TID = s.value
GROUP BY o.DoNo
英文:
You can try with STRING_AGG()
Select o.DoNo,STRING_AGG(t.Transporter,',') as tpt
From DO o
outer apply String_Split(o.Transporter,' ') s
left join Transporter as t on t.TID = s.value
GROUP BY o.DoNo
答案2
得分: 0
这是另一种只使用 GROUP BY 和 STRING_AGG 的解决方案：
select DOID, STRING_AGG(t.Transporter,',') as tpt
from TableDO d
inner join TblTransporter t on cast(d.TranID as varchar) like concat('%',t.TranID, '%')
group by DOID
结果：
DOID tpt
1 ABC Tran,BBC Tran,CBC Tran
2 BBC Tran,DBC Tran
英文:
This is an other solution using GROUP BY and STRING_AGG only :
select DOID, STRING_AGG(t.Transporter,',') as tpt
from TableDO d
inner join TblTransporter t on cast(d.TranID as varchar) like concat('%',t.TranID, '%')
group by DOID
Result :
DOID tpt
1 ABC Tran,BBC Tran,CBC Tran
2 BBC Tran,DBC Tran
答案3
得分: 0
这段代码的目的是从tranIds列表创建一个XML，然后使用nodes函数拆分它，最后使用FOR XML PATH将其连接起来。
英文:
Here's a solution for all those who still think SQL Server 2016 is the latest and greatest version:
DECLARE @do TABLE (doid int, tranid varchar(100))
DECLARE @tblTransporter TABLE (tranID int, transporter varchar(100))
INSERT INTO @do
SELECT doid, tranid
FROM (
VALUES (1,'1 2 3')
, (2,'2 4')
) t (DOID,TranID)
INSERT INTO @tbltransporter
SELECT tranid,Transporter
FROM (
VALUES (1,'ABC Tran')
, (2,'BBC Tran')
, (3,'CBC Tran')
, (4,'DBC Tran')
) t (TranID,Transporter)
SELECT doid
, STUFF((SELECT ISNULL(',' + t.transporter, '')
FROM (
SELECT cast('<root><i>' + replace(tranid, ' ', '</i><i>') + '</i></root>' AS xml) x
) x
CROSS apply x.nodes('root/i') n(n)
LEFT JOIN @tblTransporter t
ON t.tranID = n.value('.', 'INT')
ORDER BY n.value('.', 'INT')
FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)'), 1, 1, '')
FROM @do
It looks a bit busy but what it does is create an xml from the list of tranIds, and then split it by using nodes, and then concatenates it back using another FOR XML PATH
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论