Lookup from a comma separated SQL column

Question

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.

Your current table design is far from optimal. Assuming that you cannot change it, I suggest you look into using [`STRING_SPLIT`](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15) function. — Alex, Dec 01 '21 at 00:24
A good rule of thumb to follow is that if you are struggling to write a query to get a pretty normal result set it is almost always because the design is fighting you. This is definitely the case here because delimited data violates 1NF. When the design of the database is well done the queries are easy to write. — Sean Lange, Dec 01 '21 at 04:36
Normalize your schema. See ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) (Spoiler: Yes, it is.). — sticky bit, Dec 01 '21 at 09:41
Thanks all - this data comes into the system as comma delimited values via a pre-existing API and set of tables. I may indeed refactor things. But also I just wanted to know how to do it. I am stubborn that way. ;) — rigoo44, Dec 01 '21 at 19:19

John Cappelletti · Accepted Answer · 2021-12-01T01:30:21.557

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

Daniel Cruz · Answer 2 · 2021-12-01T01:16:54.423

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

STRING_SPLIT allows you to convert a NVARCHAR and split it into a table where each row is a value, and STRING_AGG allows you to do the opposite, Join a table into a NVARCHAR. Then I just used a JOIN in between.

Maybe its not the cleanest solution but it does the job. Also its a bit inefitient but using native functions instead of loops help a lot.

I attach a working example. In this online editor I just had one table so I joined it with itself but it must work joining with other tables.

SELECT 
    *,
    (
  SELECT STRING_AGG(CAST(Val AS NVARCHAR), ', ') -- concatenates the rows together
  FROM
  (
    SELECT demo.hint AS Val
    FROM STRING_SPLIT((SELECT d.name FROM demo as d where id = demo.id), ',') -- splits the row by comas
    JOIN demo ON value = demo.id -- joins so that the values are replaced with names
  ) Vals
) as JointValues -- name of the column with the joint values
FROM demo

Thanks, this was really helpful but when it came time to figure out the join on multiple tables I ran into issues. — rigoo44, Dec 01 '21 at 19:11

Lookup from a comma separated SQL column

2 Answers2