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

WaldaIncAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
This should do:
with cte as (
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)
select t1.ID, t1.DataID, 
(select substring (', ' + t2.ImproperCode, 3, 1000)
from cte t2
where t1.ID = t2.ID and t1.DataID = t2.DataID
for xml path('')) as code
from cte t1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dqmqCommented:
This should nudge you closer:
with cte as (
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)
--
select stuff((select ',' + t2.ImproperCode
from cte t2
where t1.ID = t2.ID and t1.DataID = t2.DataID
for xml path('')),1,1,'') as code
,t1.ID, t1.DataID 
from cte t1
group by t1.id, t1.dataID

Open in new window

0
WaldaIncAuthor Commented:
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?
0
WaldaIncAuthor Commented:
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.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Any ideas on how to wrap this code into an inner select?

can you kindly explain more so that I can help you out..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.