SQL Split, Join & Merge

Question

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.

Which type of database are you using? `MySQL` or `SQL Server`? — flyingfox, May 17 '23 at 06:55

score 1 · Answer 1 · answered May 17 '23 at 06:57

1

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

answered May 17 '23 at 06:57

flyingfox

13,414
3
24
39

Glad it can help you,can you mark it as accepeted? – flyingfox May 17 '23 at 07:08

score 0 · Answer 2 · answered May 17 '23 at 07:41

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

Demo here

score 0 · Answer 3 · answered May 17 '23 at 08:29

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

SQL Split, Join & Merge

3 Answers3