Combining multiple rows within nested select

Question

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!

@Serg My apologies, I attempted to strip out long table names for easier readability. I believe they are all matching now. — evade, Jul 26 '16 at 17:31
See http://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server — Serg, Jul 26 '16 at 17:44
Possible duplicate of [Simulating group\_concat MySQL function in SQL Server?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server) — Tab Alleman, Jul 26 '16 at 18:01
If this was a duplicate, I apologize. If I knew about group_concat and or XML path it could of been avoided. Testing the answers and information out now! — evade, Jul 26 '16 at 18:30
@evade:You can frame your question much better going forward ,please look here ..https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — TheGameiswar, Jul 26 '16 at 18:44

score 3 · Accepted Answer · answered Jul 26 '16 at 17:44

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'

Combining multiple rows within nested select

1 Answers1