SQL select joined string for set of ids

Question

I have to deal with a mssql database and the information given in a table like this:

Users:

ID Name    Countries
--------------------
1  User1   1,2,3
2  User2   2,5

Countries:
ID Country
----------
1  Australia
2  Germany
3  USA
4  Norway
5  Canada

Now, what i am looking for is a select statement that will give me a result like this:

Result:
ID User   CountriesByName
-----------------------------
1  User1  Australia, Germany, USA
2  User2  Germany, Canada

I'd prefer a solution that won't depend on special MSSQL syntax over something special, but there is no way for me to use some LINQ-magic :(

Answer 1

Try a Common Table Expression query. Simple-Talk has a very nice walkthrough that explains different approaches to SQL contacenation and gives examples of using CTEs (look for the WITH statements).

Answer 2

First, you'll need to split that string up. Here's a workable split function:

Create Function [dbo].[split]
    (@input   varChar(8000)        -- List of delimited items
    ,@delimit varChar(8000) = ',') -- delimiter that separates items
Returns @List Table ([item] varChar(8000)) As
Begin

Declare @item VarChar(8000);

while charIndex(@delimit, @input, 0) <> 0 Begin

    Select
     @item  = rTrim(lTrim(subString(@input, 1, charIndex(@delimit, @input, 0) - 1))),
     @input = rTrim(lTrim(subString(@input, charIndex(@delimit, @input, 0) + Len(@delimit), Len(@input))));

    If Len(@item) > 0 Insert Into @List Select @item

End

If Len(@input) > 0 Insert Into @List Select @input

Return;

End

Then you'll need to join the values back to your countries table, and re-join them. This will get you most of the way:

Select ID
      ,[Name] as [User]
      ,(
        Select [country] + ', '
        From [Countries]
        Where [ID] In (
            Select Cast([item] As Integer)
            From dbo.split(U.Countries, ',')
            Where IsNumeric(item) = 1)
        Order By [country]
        For XML Path('')) As [CountriesByName]
From [Users] As U

However, that leaves a trailing comma. You may want to remove that on some other layer, but just in case you MUST do it in SQL, this should work:

Select ID
      ,[User]
      ,Left([CountriesByName], Len([CountriesByName]) - 1) As [CountriesByName]
From (
    Select ID
       ,[Name] as [User]
       ,(
      Select [country] + ', '
      From [Countries]
      Where [ID] In (
       Select Cast([item] As Integer)
       From dbo.split(U.Countries, ',')
       Where IsNumeric(item) = 1)
      Order By [country]
      For XML Path('')) As [CountriesByName]
    From [Users] As U) As [Results]

Answer 3

If you normalise your data into three tables then the following works for what you want to do. This is using my own schema as I had to knock up some tables to test it).

Select
UserId, UserName,
(
 Select
  CountryName + ',' 
 From
  Country As C
 Inner Join
  UserCountry As UC
 On C.CountryId = UC.CountryId
 Where
  UC.UserId = [User].UserId
 ORDER BY
  CountryName
 FOR XML PATH('')
) As Countries

From [User];

Answer 4

First I would make a stored proc that takes a string of country ids and returns the list of country names using dynamic sql.

create proc dbo.getCoutries(@ids nvarchar(200))
as
begin
declare @sql  nvarchar(200)
set @sql = 'select @countries = @countries  + Country+
    '', '' from Countries where ID in ('+@ids+')'
declare @countries nvarchar(200)
set @countries = ''
DECLARE @ParmDefinition nvarchar(500);
SET @ParmDefinition = N'@countries nvarchar(200) OUTPUT';


EXECUTE sp_executesql @sql, 
   @ParmDefinition, 
   @countries=@countries OUTPUT;

select substring(@countries,1,len(@countries)-1) Countries

end

go

So now exec getCoutries '2, 5' returns "Germany, Canada". Once that is done we now need to run the code below to return the table that looks like:

Name     Countries
User1   Australia, Germany, USA
User2   Germany, Canada



--The variable we will use to loop thru the user ids
declare @userId int
set @userId = 0

-- hold the string of country ids
declare @countryIds varchar(30)

-- a temp table that we will populate
-- it will have the users ID and then the 
-- string of countries (i.e "Germany, Canada")
declare @results as 
table 
(userId int, 
countries varchar(200))


--Loop thru each row in the Users table.
while 1=1
begin
select @userId=min(ID)
from Users
where ID > @userId

if @userId is null
    break

--get the string of country ids for this user
select @countryIds=Countries
from Users
where ID = @userId

--use our stored proc to bring back the string of names
insert into @results
(countries)
exec getCoutries @countryIds

--update the userId for our new row
update @results
set UserId = @UserId
where UserId is null


end

-- return the user and his list of countries
select u.Name, r.Countries
from Users u
inner join @results r
on u.ID = r.UserId

GO