MS SQL SELECT and Group By Assistance


I have a table with three fields (ID, KEY, VALUE) and the ID field is not a unique ID. It is similar to the this:

|   ID   |   KEY   |   VALUE   |
  1          name      bob
  1          name      jim
  1          name      bill
  2          name      mike
  2          name      harry
  3          name      meagan
  3          name      jennifer

I need help with writing a SQL query that would go through and return to me all of the values separated by comma by the ID. For example:

1 bob,jim,bill
2 mike,harry
3 meagan, jennifer

Who is Participating?
SharathData EngineerCommented:
If you are using SQL Server 2005, this will work
select distinct id,rtrim(substring(isnull((select ','+VALUE from YourTable t2 where = for xml path('')),' '),2,2000)) VALUE
from YourTable t1

Open in new window

Christopher GordonSenior Developer AnalystCommented:
--you can paste below this into SSMS (btw I didn't write this but just adapted to your question <--very cool read

---declaring temp table

declare @myTable table (id int, keyValue varchar(4), myValue varchar(100))

--loading temp table
insert into @myTable
select 1, 'Name', 'Bob'
select 1, 'Name', 'jim'
select 1, 'Name', 'Bill'
select 2, 'Name', 'Mike'
select 2, 'Name', 'Harry'
select 3, 'Name', 'Meagan'
select 3, 'Name', 'Jennifer'

--return results from temp table
select      *
from      @myTable
--the cool code that makes this happen
;WITH Ranked ( Id, rnk, myValue )  
             AS ( SELECT Id,
                         ROW_NUMBER() OVER( PARTITION BY Id ORDER BY Id ),
                         CAST( myValue AS VARCHAR(8000) )
                    FROM @myTable),
   AnchorRanked ( Id, rnk, myValue )  
             AS ( SELECT id, rnk, myValue
                    FROM Ranked
                   WHERE rnk = 1 ),
    RecurRanked ( Id, rnk, myValue )
             AS ( SELECT Id, rnk, myValue
                    FROM AnchorRanked
                   UNION ALL
                  SELECT Ranked.Id, Ranked.rnk,
                         RecurRanked.myValue+ ', ' + Ranked.myValue
                    FROM Ranked
                   INNER JOIN RecurRanked
                      ON Ranked.Id = RecurRanked.Id
                     AND Ranked.rnk = RecurRanked.rnk + 1 )
    SELECT Id, MAX( myValue )
      FROM RecurRanked
     GROUP BY Id;
SharathData EngineerCommented:
Or you can try like this. create a function like this and call the function in code as below.

select ID,dbo.fn_Value(ID)
from Table_1
group by ID  

create function fn_Value(@ID int) returns varchar(2000) as
DECLARE @listStr VARCHAR(2000)
 SELECT @listStr = COALESCE(@listStr+',' ,'') + Value FROM Table_1 where ID = @ID
 return @listStr

Open in new window

NursingCorpAuthor Commented:
that did it
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.