MS SQL SELECT and Group By Assistance

Hello,

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

NursingCorpAsked:
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 t1.id = t2.id for xml path('')),' '),2,2000)) VALUE
from YourTable t1

Open in new window

0
 
Christopher GordonSenior Developer AnalystCommented:
--you can paste below this into SSMS (btw I didn't write this but just adapted to your question http://www.projectdmx.com/tsql/rowconcatenate.aspx) <--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'
union
select 1, 'Name', 'jim'
union
select 1, 'Name', 'Bill'
union
select 2, 'Name', 'Mike'
union
select 2, 'Name', 'Harry'
union
select 3, 'Name', 'Meagan'
union
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;
0
 
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
begin
DECLARE @listStr VARCHAR(2000)
 SELECT @listStr = COALESCE(@listStr+',' ,'') + Value FROM Table_1 where ID = @ID
 return @listStr
 end
 

Open in new window

0
 
NursingCorpAuthor Commented:
Thanks,
that did it
0
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.