Hitesh Hitesh - 3 months ago 6
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
roletypeid
in
usertable
as comma-separated as user can have multiple roles.

Please find below detail images:

enter image description here

Any help would be appreciate.

Thanks,

Answer

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">
  <r>1</r>
  <r>2</r>
</id>
<id userid="2">
  <r>1</r>
  <r>3</r>
</id>
<id userid="3">
  <r>2</r>
  <r>3</r>
</id>

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,
        rt.Name
FROM @xml.nodes('/id/r') as t(v)
INNER JOIN RoleType rt
    ON rt.ID = t.v.value('.','bigint')
)

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

Output:

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