• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

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

  • 2
1 Solution
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'
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:
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

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now