Lookup from a comma separated SQL column-CodePudding

I have a user table which has comma separated ids in one of the columns, like:

Id	Name	PrimaryTeamId	SecondaryTeamIds
1	John	123	456,789,669
2	Ringo	123	456,555

and a secondary table which contains the team names

Id	TeamId	TeamName
1	456	Red Team
2	669	Blue Team
3	789	Purple Team
4	555	Black Team
5	123	Orange Team

I'm trying to create a view which gives the following format:

Name	Primary Team	Secondary Teams
John	Orange Team	Red Team, Purple Team, Blue Team
Ringo	Orange Team	Red Team, Black Team

I have created

select
  u.Name,
  t.TeamName as 'Primary Team'
  SELECT ... ?? as 'Secondary Teams'
from
   users u
inner join teams t on u.PrimaryTeamId = t.TeamId

I've tried numerous things but can't seem to put it together. I can't seem to find the same use case here or elsewhere. I do control the data coming in so I could parse those values out relationally to begin with or do some kind of lookup on the ETL side, but would like to figure it out.

CodePudding user response：

I played around with this a little bit and I found you can do it using two functions,

CodePudding user response：

If the sequence of Secondary Teams is essential, you can parse the string via OpenJSON while preserving the sequence [key].

Then it becomes a small matter of string_agg()

Example or dbFiddle

Select A.ID
      ,A.Name
      ,PrimaryTeam = C.TeamName
      ,B.SecendaryTeams
 from  YourTable A
 Cross Apply (
                Select SecendaryTeams = string_agg(B2.TeamName,', ') within group (order by B1.[Key])
                 From  OpenJSON( '["' replace(string_escape([SecondaryTeamIds],'json'),',','","') '"]' ) B1
                 Join  YourTeams B2 on B1.Value=B2.TeamID
             ) B
 Join YourTeams C on A.[PrimaryTeamId]=C.TeamId

Results

ID  Name    PrimaryTeam     SecendaryTeams
1   John    Orange Team     Red Team, Purple Team, Blue Team
2   Ringo   Orange Team     Red Team, Black Team