• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

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

0
WaldaInc
Asked:
WaldaInc
  • 2
  • 2
2 Solutions
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
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 & ArchitectCommented:
>> 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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now