concatenate join

[peac3]

Hi all,

I need to join from the concatenate column separated by space delimiter.

Input

Table1
ClientID     CityCode
1            2011 2012 2013
2            2011 2012   

Table2 (ref table)
CityCode   CityName
2011       Sydney
2012       Melbourne
2013       Perth

After joined 2 tables above, I would like the output result like below

Table3
ClientId  Cityname
1         Sydney Melbourne Perth 
2         Sydney Melbourne

Any help with query would be appreciated.

Thanks guys,

 

[bborissov]

If you use SQL Server 2017 check STRING_AGG() function

Borislav Borissov
VFP9 SP2, SQL Server

 

[peac3]

Hi bborissov,

I am using sql 2012 or teradata so native sql needed for this.

Thanks

 

[dhookom]

Others in this group have helped me with something similar in the past. The first step is to normalize your data so you don't have a column with multiple CityCodes. Then use STUFF and XML PATH with the normalized result.

WITH
cteNorm AS
(
SELECT ClientID ,CityName
FROM Table1,Table2 
WHERE Table1.CityCode Like '%' +Table2.CityCode +'%'
)
SELECT DISTINCT ClientID,
REPLACE(STUFF((SELECT DISTINCT '; '+ CityName
	FROM cteNorm N WHERE N.ClientID = cteNorm.ClientID 
	FOR XML PATH('')),1,2,''),'&','&') as Cities
FROM cteNorm

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016