evade evade - 4 months ago 7
SQL Question

Combining multiple rows within nested select

I have the following (poorly structured) tables:

tbl.quotes A
quoteid | assignedID | created_by | name
int, int, varchar, varchar

tbl.quote_notes B
id | quoteID | userID | noteText
int, int, varchar, text

tbl.admins C
id | name
int, varchar

tbl.agents D
id | name
int, varchar


I have the following query, but I need to combine the multiple rows for each quote_notes.noteText on each result for quotes.

SELECT
A.quoteid,
A.name,
C.name,
D.name,
(SELECT
TOP 1 notetext
FROM quote_notes B
WHERE B.quoteid = A.quoteid
ORDER BY dateentered DESC
) AS [notes]
FROM quotes A
LEFT JOIN admins C
ON A.assignedid = C.id
LEFT JOIN agents D
ON A.created_by = D.id
WHERE A.createuserid = 'agentname'


This yields and does not combine quote_notes.noteText multiple rows:

12345 | Quote Name | Admin Name | Agent Name | Notes


Desired Result:

12345 | Quote Name | Admin Name | Agent Name | Note1, Note2, Note3


I had wrote a while loop to combine the rows, but cannot get it to work within a nested select statement as it uses variables. It also only works with 1 quoteid

DECLARE @rowcount INTEGER
DECLARE @Count INTEGER
DECLARE @note VARCHAR(MAX)
SET @Count = 1
SET @note = ''
SET @rowcount = (SELECT COUNT(quoteID) FROM quote_notes WHERE quoteID = '12345')

WHILE @Count<=@rowcount
BEGIN
IF @note!=''
SET @note = @note+',' + (SELECT convert(varchar(max), noteText) FROM quote_notes WHERE id = @Count)
ELSE
SET @note = (SELECT noteText FROM quote_notes WHERE id = @Count)
SET @Count=@Count+1
END
SELECT @note AS note


Any help would be appreciated, thanks!

Answer

Use for XML Path..

SELECT 
    A.quoteid,
    A.name,
    C.name,
    D.name,
        STUFF((SELECT 
        ',' +notetext
     FROM quote_notes B
     WHERE B.quoteid = A.quoteid
     for xml path('')
     ),1,1,'')
     AS [notes]
    FROM quotes A
    LEFT JOIN admins C
        ON A.assignedid = C.id
    LEFT JOIN agents D
        ON A.created_by = D.id
WHERE A.createuserid = 'agentname'
Comments