Using MS SQL Server 2005, how can I consolidate detail records into a single comma separated list

BACKGROUND:I am running MS2005. I have a MASTER table (ID, MDESC) and a DETAIL table (MID, DID, DDESC) with data as follows

1 MASTER_1
2 MASTER_2
1 L1 DETAIL_M1_L1
1 L2 DETAIL_M1_L2
1 L3 DETAIL_M1_L3
2 L1 DETAIL_M2_L1
2 L2 DETAIL_M2_L2

If I join the tables with

SELECT M.*, D.DID FROM MASTER M INNER JOIN DETAIL D on M.ID = D.MID

I get a list like the following:

1 MASTER_1 L1
1 MASTER_1 L2
1 MASTER_1 L3
2 MASTER_2 L1
2 MASTER_2 L2

QUESTION: Is there any way to use a MS SQL select statement to get the detail records into a comma separated list like this:

1 MASTER_1 "L1, L2, L3"
2 MASTER_2 "L1, L2"

From stackoverflow

• coalesce is your friend.

  declare @CSL vachar(max)
  
  set @CSL = NULL
  select @CSL = coalesce(@CSL + ', ', '') + cast(DID as varchar(8))
  from MASTER M INNER JOIN DETAIL D on M.ID = D.MID
  
  select @CSL
  

  This will not work well for a generalized query (i.e. works great for a single master record).

  You could drop this into a function... but that may not give you the performance you need/want.

  Bill Karwin : That's clever, but it only works for one string at a time.

  cmsjr : He could wrap something like the above into a function that takes the id from the master table as an input, and returns the comma delimited string.

  Noah : Nice, but returns all on one line, like "L1, L2, L3, L1, L2" I need it grouped by the master rows

• This is the purpose of MySQL's GROUP_CONCAT() aggregate function. Unfortunately, it's not very easy to duplicate this function in other RDBMS brands that don't support it.

  See http://stackoverflow.com/questions/451415/simulating-groupconcat-mysql-function-in-ms-sql-server-2005

  Noah : Thanks for the link, there was some new functionality added to SQL Server with the CROSS APPLY that I never noticed before.

• I think you need a Fucntion for this to work properly in recent version of SQL Server:

  http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

  Noah : Thanks what I thought as well, but see the pure SQL solution posted

• You need a function:-

   CREATE FUNCTION [dbo].[FN_DETAIL_LIST]
   (
       @masterid int
   )
   RETURNS varchar(8000)
   AS 
   BEGIN
       DECLARE @dids varchar(8000)
  
       SELECT @dids = COALESCE(@dids + ', ', '') + DID
       FROM DETAIL
       WHERE MID = @masterid
       RETURN @dids
   END
  

  Usage:-

  SELECT MASTERID, [dbo].[FN_DETAIL_LIST](MASTERID) [DIDS]
  FROM MASTER
  

  Noah : See posted solution without function ... very slick

  AnthonyWJones : This highlights why its good to specify versions in questions. APPLY is a SQL 2005 thing. SQL Servers tend to have a long life and hence in absence of version info I go with a SQL 2000 compatible answer.

  Noah : Nice point, I'll update the question to reflect this.

  CRice : Yes this is a solution I will use due to having SQL Server 2000, thanks

• Thanks to the concept in the link from Bill Karwin, it's the CROSS APPLY that makes it work

  SELECT ID, DES, LEFT(DIDS, LEN(DIDS)-1) AS DIDS
   FROM MASTER M1 INNER JOIN DETAIL D on M1.ID = D.MID 
    CROSS APPLY (
      SELECT DID + ', '
      FROM MASTER M2 INNER JOIN DETAIL D on M2.ID = D.MID 
      WHERE M1.ID = M2.ID
      FOR XML PATH('')
     ) pre_trimmed (DIDS)
  GROUP BY ID, DES, DIDS
  

  RESULTS:

  ID  DES        DIDS
  --- ---------- ---------------
  1   MASTER_1   L1, L2, L3
  2   MASTER_2   L1, L2
  

  AnthonyWJones : I agree it is quite slick, although the reliance on XML is a little distrubing but if works well enough without killing performance its a good solution.