asked on

SQL Syntax - Concatenate Rows

Hello.

This is my SQL statement

SELECT i.ImproperCode, nr.ID, nrd.ID as DataID
FROM NoticeReturnsData_ImproperReason ir
LEFT JOIN NoticeReturnsData nrd
      ON ir.NoticeReturnsDataID = nrd.ID
LEFT JOIN NoticeReturns nr
      ON nrd.NoticeReturnID = nr.ID
LEFT JOIN ImproperReason i
      ON ir.ImproperReasonID = i.ImproperReasonID
WHERE nr.PersonID = 487890
AND i.ImproperCode is not null

The data set it returns is this:
Code ID DataID
NoSig      1      3
NonMem      1      4
NoSig      1      4

Note the DataID column. What I would like to do, is where the DataID column is the same, concactenate the Code values into one value with a Pipe (or comma) between them.

So the result set would be like this:

Code ID DataID
NoSig       1      3
NonMem|NoSig      1      4

Any help on this is greately appreciated

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

dqmq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

WaldaInc

ASKER

These both work great. Thanks for the help.

However, how would I use this SQL as an Inner Select? Once I use the paranthesis to start the Inner Select, it doesn't like the syntax of the

with cte as ...

Any ideas on how to wrap this code into an inner select?

WaldaInc

ASKER

I'm splitting the points between the two of you. Hope neither of you two mind, if you do contact me.

The solution works, although to get the inner select I wanted I had select the data into a temp table, then back out of the temp table in the inner select.

Thanks to both of you for responding so quickly.

Raja Jegan R

>> Any ideas on how to wrap this code into an inner select?

can you kindly explain more so that I can help you out..