Mutliple SQL Server Rows into One Row Contactenating Strings

Using SQL Server 2017 Standard Edition.

I have the following in a SQL Server table:

PersonID        EncounterID        Positive      comments
GUIDxx1         GUIDxxa               "Positive"     "free text comment1"
GUIDxx1         GUIDxxa               "Negative"   "free text comment2"
GUIDxx1         GUIDxxa               "Positive"     "free text comment3"  
GUIDxx1         GUIDxxb               "Negative"   "free text comment4"
GUIDxx1         GUIDxxb               "Positive"     "free text comment5"
GUIDxx1         GUIDxxb               "Positive"     "free text comment6"
GUIDxx2         GUIDyya               "Positive"      "free text comment7"
GUIDxx2         GUIDyya               "Positive"      "free text comment8"

I need the following result set:

PersonID       EncounterID        comments
GUIDxx1        GUIDxxa               "free text comment1 free text comment3"
GUIDxx1        GUIDxxb               "free text comment5 free text comment6"
GUIDxx2        GUIDyya               "free text comment7 free text comment8 "   

Note that any row where the Positive column is not positive is to be excluded.  And, yes, that row contains either the word Positive or the word Negative whereas it should have been a bit.  :(  Also, the numbers in "free text comment" don't mean anything.  Those numbers are there to make it easier for you to tell the comments apart.

Anyway, what do you guys think?

JamesNT
JamesNTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Mark WillsTopic AdvisorCommented:
How about something like :
select PersonID, EncounterID, (select comments+' ' from your_data S where s.personid = d.personID and s.encounterID = d.encounterID for XML path(''))
from your_data D
where Positive = 'Positive'
group by PersonID, EncounterID

Open in new window

NorieAnalyst Assistant Commented:
Try this.

SELECT PersonID, EncounterID, Positive, (SELECT comments+',' from Table_1 S WHERE S.PersonID = T.PersonID AND S.EncounterID=T.EncounterID AND S.Positive='Positive' FOR XML path(''))
FROM Table_1 T
WHERE Positive='Positive'
GROUP BY PersonID, EncounterID, Positive

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
JamesNTAuthor Commented:
Excellent work.  Mark was so close it wasn't even funny.  Left out part of the WHERE clause in inner select statement but Norie caught it.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Mark WillsTopic AdvisorCommented:
Shouldnt need it because it is linking back to the source data 'D' where the 'negative' have already been excluded....
JamesNTAuthor Commented:
Unfortunately, I was getting strings from rows where Positive = 'Negative' which I didn't want.  I thought I had done something wrong and came back to double check when I saw Norie's answer and noticed the additional WHERE in the inner SELECT.

JamesNT
Mark WillsTopic AdvisorCommented:
There are duplicate rows which I assumed to be a typo, after all, how can the same encounter id be both (well, that's what I thought) :)

('GUIDxx1','GUIDxxa','Negative','free text comment2'),
('GUIDxx1','GUIDxxa','Positive','free text comment3'),
and
('GUIDxx1','GUIDxxb','Negative','free text comment4'),
('GUIDxx1','GUIDxxb','Positive','free text comment5'),

So, the example data, which I assumed was not real life (resulting in typo masking real life)... Was possibly missing different dates or sessions or some other criteria. Like a primary key... Or maybe GUID-ID's implies primary keys. Thats my error in 'assuming', hence my previous comment.

Because we should link back (in the in-line sub-query) to the primary key in an ideal would. Otherwise, yes, the extra check for Positive is required, and take a hit on performance if indexes / data needs to be scanned due to the additional 'where'. If you do have large data sets, then you may want to revisit and incorporate unique index....

Anyway, No excuses for incorrect assumptions, and I should have taken the data as is.

You now have your working solution and congrats to Norrie for adding the additional 'where'.
JamesNTAuthor Commented:
Mark,

Thank you for the additional explanation.  That will prove helpful for future onlookers who run across this question (EE often appears in my Bing and Google searches).  

Hence why you got the assisted solution.  :)

JamesNT
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
SQL

From novice to tech pro — start learning today.