CODEWITHSUNDEEP
sqlsql-server
evade
evade

Reputation: 139

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!

Upvotes: 1

Views: 75

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28900

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'

Upvotes: 3

Related Questions