Group Concat

Question

Group Concat

lanky_doodle

SSCrazy

Points: 2378
More actions
March 3, 2016 at 2:16 am

#305451

Hey,
I have a query that is returning 3 rows (because there are 3 owners). I need to concat the 3 owners into a single value, which would return 1 row.
I can't work this out:
The CN_O join is what's returning the 3 owners. I need the result to be this:
P40657MT (The Institute of Cancer Research: Royal Cancer Hospital; Astex Therapeutics Limited; Cancer Research Technology Limited - P0033(MT)):
Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Since I can't see the picture you attached, my advice can only be very general: use a FOR XML clause to concatenate values from different rows into a single row.

John

lanky_doodle SSCrazy Points: 2378 More actions · Answer 2

Yeah that's what I'm doing.

Direct link: http://i.imgur.com/gczrdrb.png

palotaiarpad SSCertifiable Points: 5617 More actions · Answer 3

palotaiarpad

SSCertifiable

Points: 5617

March 3, 2016 at 3:39 am

#1862677

You should do the same in the outer query.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

lanky_doodle (3/3/2016)
Hey,
I have a query that is returning 3 rows (because there are 3 owners). I need to concat the 3 owners into a single value, which would return 1 row.
I can't work this out:
The CN_O join is what's returning the 3 owners. I need the result to be this:
P40657MT (The Institute of Cancer Research: Royal Cancer Hospital; Astex Therapeutics Limited; Cancer Research Technology Limited - P0033(MT)):
Thanks!

I can see the picture. Can you post up a text version of your query please?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

lanky_doodle SSCrazy Points: 2378 More actions · Answer 5

Thanks for the replies guys. I think I'm there:

Direct link: https://www.sqlservercentral/Forums/Uploads/Images/1766297-1.png