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

Question

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:

Any help would be appreciate.

Thanks,

Fix you database structure. Check this up: http://stackoverflow.com/questions/444251/how-to-store-a-list-in-a-db-column — cha, Aug 12 '16 at 04:45
please post expected result ,current result and DDL,DML to repro the issue. — TheGameiswar, Aug 12 '16 at 04:45
Storing "comma-separated" lists of ID's in a single column is a ***horribly bad*** design - don't do it. Use the **proper, relational** way of handling this! Stuffing a list into a single columns will cause you endless grief and trouble - just don't do it. Seriously. — marc_s, Aug 12 '16 at 04:58

score 1 · Accepted Answer · answered Aug 12 '16 at 06:07

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

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

1 Answers1