Fetch the Records from the comma seperated column from the table through stored procedure

Question

I have a a table that store values like this:

Name      CategoryId
Gaurav    4,6
Amit      2,4
Ajay      6,2

2,4,6 (This is the id of category that name is present in their master table)

The Master table of category like this.

Id  CategoryName
2   Motor
4   Scooter
6   Car

I want to fetch all the records from the table first, and want to category name ( not the category id).

Name       CategoryName
Gaurav     Scooter, Car
Amit       Motor, Scooter
Ajay       Car, Motor

How this is done through Stored Procedure...

score 1 · Answer 1 · answered Apr 02 '13 at 11:38

Something like this might work:

with peopleCategory as
(
  select p.Name
    , c.CategoryName
  from people p
    inner join category c on charindex(',' + cast(c.Id as varchar(100)) + ','
                                     , ',' + p.CategoryId + ',') > 0
)
select p.Name
  , CategoryName = stuff
    (
      (
        select ',' + pc.CategoryName
        from peopleCategory pc
        where p.Name = pc.Name
        order by pc.CategoryName
        for xml path('')
      )
      , 1
      , 1
      , ''
    )
from people p
order by Name

SQL Fiddle with demo.

However, you would be much, much better off to normalise these tables into a sensible structure so you can query them without the complexity required above.

Fetch the Records from the comma seperated column from the table through stored procedure

1 Answers1