Hitesh Hitesh - 1 year ago 51
SQL Question

Get data from other table when id save in main table as comma seperated

I have two tables like this:

  • RoleType
    with columns ID int, Name varchar(50)

  • Usertable
    with columns Id bigint, RoleTypeID as varchar(50)

I have
as comma-separated as user can have multiple roles.

Please find below detail images:

enter image description here

Any help would be appreciate.


Answer Source

As suggested in comments you must re-design your table structure to avoid comma-separated values.

Here is a solution for your answer, should work in SQL Server 2008.

At first convert table to XML format:

DECLARE @xml xml

SELECT @xml = (
    SELECT  CAST('<id userid="'+CAST(ID as nvarchar(max))+'"><r>'+REPLACE(RoleTypeID,',','</r><r>')+'</r></id>' as xml)
    FROM usertable
    FOR XML PATH('')

This part will give you XML like this:

<id userid="1">
<id userid="2">
<id userid="3">

Then we can use CTE to convert ID with comma separated roles in normalized table, and join with RoleType table to get role names:

;WITH cte AS (
SELECT  t.v.value('../@userid','bigint') as ID,
FROM @xml.nodes('/id/r') as t(v)
INNER JOIN RoleType rt
    ON rt.ID = t.v.value('.','bigint')

        STUFF((SELECT ','+Name
        FROM cte
        WHERE ID = c.ID
        FOR XML PATH('')
        ),1,1,'') as Roles
FROM cte c


ID  Roles
1   Admin,Doctor
2   Admin,Nurse
3   Doctor,Nurse