none
How to combine values of a column in one row separated by comma

    Question

  • Hi,

    I am new to SQL Server,

    In my winform application I have to fetch data from database(sql server 2008) and bind to the grid.

    Following is my table:

    DictatorID hospitalId  patientID 

    1                  1               1

    1                    1              2

    1                   1               3

    2                 2                   1

    2                 2                      2

     

    Expected result:Get result for hospitalID

    hospitalId  DictatorID   PatientID

    1                   1            1,2,3

    2                  2               1,2

     

     

    Please suggest some solution to achieve this using stored procedure or something else.

     

     

     

    Thursday, August 04, 2011 6:47 AM

Answers

  • CREATE  TABLE  #mable(mid INT, token nvarchar(16))
    INSERT INTO #mable VALUES (0, 'foo')
    INSERT INTO #mable VALUES(0, 'goo')
    INSERT INTO #mable VALUES(1, 'hoo')
    INSERT INTO #mable VALUES(1, 'moo')
    SELECT m1.mid,
           ( SELECT m2.token + ','
               FROM #mable m2
              WHERE m2.mid = m1.mid
              ORDER BY token
                FOR XML PATH('') ) AS token
      FROM #mable m1
     GROUP BY m1.mid ;

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Vishal Gajjar Thursday, August 04, 2011 7:59 AM
    • Marked as answer by Stephanie Lv Thursday, August 11, 2011 9:01 AM
    Thursday, August 04, 2011 6:48 AM
    Answerer
  • In addition to Uri's query, to remove the last ',', you can use STUFF.

    SELECT m1.mid,
        STUFF(( SELECT ',' + m2.token
          FROM #mable m2
         WHERE m2.mid = m1.mid
         ORDER BY token
          FOR XML PATH('')),1,1,'' ) AS token
     FROM #mable m1
     GROUP BY m1.mid ;
    

    http://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/


    - Vishal

    SqlAndMe.com

    • Proposed as answer by Surendra Nath GM Thursday, August 04, 2011 1:29 PM
    • Marked as answer by Stephanie Lv Thursday, August 11, 2011 9:01 AM
    Thursday, August 04, 2011 7:59 AM

All replies

  • CREATE  TABLE  #mable(mid INT, token nvarchar(16))
    INSERT INTO #mable VALUES (0, 'foo')
    INSERT INTO #mable VALUES(0, 'goo')
    INSERT INTO #mable VALUES(1, 'hoo')
    INSERT INTO #mable VALUES(1, 'moo')
    SELECT m1.mid,
           ( SELECT m2.token + ','
               FROM #mable m2
              WHERE m2.mid = m1.mid
              ORDER BY token
                FOR XML PATH('') ) AS token
      FROM #mable m1
     GROUP BY m1.mid ;

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Vishal Gajjar Thursday, August 04, 2011 7:59 AM
    • Marked as answer by Stephanie Lv Thursday, August 11, 2011 9:01 AM
    Thursday, August 04, 2011 6:48 AM
    Answerer
  • In addition to Uri's query, to remove the last ',', you can use STUFF.

    SELECT m1.mid,
        STUFF(( SELECT ',' + m2.token
          FROM #mable m2
         WHERE m2.mid = m1.mid
         ORDER BY token
          FOR XML PATH('')),1,1,'' ) AS token
     FROM #mable m1
     GROUP BY m1.mid ;
    

    http://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/


    - Vishal

    SqlAndMe.com

    • Proposed as answer by Surendra Nath GM Thursday, August 04, 2011 1:29 PM
    • Marked as answer by Stephanie Lv Thursday, August 11, 2011 9:01 AM
    Thursday, August 04, 2011 7:59 AM
  • Hello,

    The solution of Uri  + Vishal is working but what is happening if the PatientId is an int and not a (n)varchar ?

    What is happening if there are more than 16 PatientId ( because of nvarchar(16)) ?

    Is it possible to get the definition of each "useful" field of the table ? ( it is possible that there is an ORDER missing on hospialId and maybe DictatorId )

    Have a nice day

    PS : a possible solution ( mainly using .Net Framework , easy as the application is a WinForm one )

    SELECT hospitalId,DictatorId,PatientId from MyTable ORDER BY hosptalId,PatientId

    The original poster (OP) will use this SELECT in a SqlCommand sqlcmd used to create a SqlDataAdapter sda.

    In VC# :

    sda = new SqlDataAdapter(sqlcmd);

    DataTable dt = new DataTable();

    sda.Fill(dt);

    Afterwards create a new datatable newdt with 3 colums, the 2 first ones are hospitalId and DictatorId ( same defintions than in dt ), the last one will contain the list of the patientid formatted as string (patientids).As we don't know the number of patientids for a hospitalid, set the value of MaxLength to -1 for patientids ( as if you were using a nvarchar(max) )

    Read the 1st row of dt.Save the values of hospitalid and dictatorid. In the String (patientidlist), load the value of patientid converted in a string.

    Read the next row . If you have the same values for hospitalid ( and maybe dictatorid ) than for the previous row, add to patientidlist a , followed by the value of patientid converted in a string.If you have not the values, use newdt.NewRow() to create a new row dtrow with the same definition than dt, load the 3 columns of this new row with the values of hospitalid,dictatorid and use dt.Rows.Add(dtrow) to add the new row.

    and continue as in the in the line Read the 1st row

    You will have only to bind newdt to your grid


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Thursday, August 04, 2011 10:26 AM
    Moderator
  • NVARCHAR(16) is just an example. If you build string concat output it is built on the fly.... and does not have a limit of 16 bytes
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, August 04, 2011 10:39 AM
    Answerer
  • Try this - Please change the <TableName> acordingly

    CREATE FUNCTION dbo.ConcatOrderProducts(@DictatorID int,@hospitalId)
    RETURNS VARCHAR(8000)
    AS
    BEGIN
        DECLARE @Output VARCHAR(8000)
        SELECT @Output = COALESCE(@Output+', ', '') + CONVERT(varchar(20), PatientID)
        FROM <TabName> ----- please change tablename here
        ORDER BY DictatorID,hospitalId
        RETURN @Output
    END
    GO

    SELECT DictatorID, hospitalId, dbo.ConcatOrderProducts(DictatorID, hospitalId)
    FROM <TableName> ----- please change tablename here
    GO


    http://uk.linkedin.com/in/ramjaddu
    Thursday, August 04, 2011 11:05 AM