2

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"
Noah
  • 15,080
  • 13
  • 104
  • 148

5 Answers5

3

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
AnthonyWJones
  • 187,081
  • 35
  • 232
  • 306
  • See posted solution without function ... very slick – Noah Feb 14 '09 at 00:24
  • 1
    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. – AnthonyWJones Feb 14 '09 at 16:52
  • Yes this is a solution I will use due to having SQL Server 2000, thanks – CRice Jul 21 '09 at 01:37
3

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
Noah
  • 15,080
  • 13
  • 104
  • 148
  • 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. – AnthonyWJones Feb 14 '09 at 16:54
1

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.

user53794
  • 3,800
  • 2
  • 30
  • 31
  • 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. – cmsjr Feb 13 '09 at 20:14
  • Nice, but returns all on one line, like "L1, L2, L3, L1, L2" I need it grouped by the master rows – Noah Feb 13 '09 at 20:15
1

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 Simulating group_concat MySQL function in Microsoft SQL Server 2005?

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks for the link, there was some new functionality added to SQL Server with the CROSS APPLY that I never noticed before. – Noah Feb 14 '09 at 00:23
0

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

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

BenMorel
  • 34,448
  • 50
  • 182
  • 322
Kristen
  • 4,227
  • 2
  • 29
  • 36