MS SQL SELECT and Group By Assistance

Posted on 2009-02-19
Last Modified: 2012-06-27

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

Question by:NursingCorp
    LVL 14

    Expert Comment

    by:Christopher Gordon
    --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;
    LVL 40

    Accepted Solution

    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

    LVL 40

    Expert Comment

    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


    Author Closing Comment

    that did it

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    This video discusses moving either the default database or any database to a new volume.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now