Return query with concatenating values.

Question

Return query with concatenating values.

djacobos

SSC Eights!

Points: 891
More actions
November 22, 2013 at 1:35 pm

#285519

I have two tables i am working with, they are "Institutions" and "InstitutionOversights". The relationship is one-to-many.
The sample data is below.
Table one:
InstitutionID, InstName
------------------------
1 School Alpha
2 School Beta
3 School Charlie
4 School Delta
Table two:
InstitutionOversightID, InstitutionID, Type
------------------------------------------------
1 1 Accreditation
2 1 Verifcation
3 1 Old System
I would like a query to return the results in the following format:
InstitutionID, InstName, TypeList
-----------------------------------------------
1 School Alpha Accreditation, Verification, Old System
2 School Beta null
3 School Charlie null
4 School Delta null

Viewing 4 posts - 1 through 3 (of 3 total)

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

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

Welcome to the forum. First off please read the article in my signature about posting questions to the forum. I have done the DDL and insert statements for you and I also provided a query to accomplish what you would like:

create table dbo.Institution (InstitutionID int, InstName varchar(256))

create table dbo.Oversight (InstitutionOversightID int, InstitutionID int, [Type] varchar(256))

insert into dbo.Institution

values (1, 'School Alpha')

, (2,'School Beta')

,(3,'School Charlie')

,(4,'School Delta')

insert into dbo.Oversight

values (1,1,'Accreditation')

,(2,1,'Verifcation')

,(3,1,'Old System')

select i.InstitutionID, i.InstName, STUFF((

SELECT ', '+CAST(Oversight.[type] AS NVARCHAR)

FROM (

SELECT [type]

FROM

dbo.Oversight o

WHERE

i.InstitutionID = o.InstitutionID

) AS Oversight

FOR XML PATH('')),1,2,'') OversightType

from dbo.Institution i

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

For explanation on the code that Keith posted, read the following article and post back if you have any more questions.

http://www.sqlservercentral.com/articles/comma+separated+list/71700/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

djacobos SSC Eights! Points: 891 More actions · Answer 3

Hi, I was searching the web while waiting on a reply and found similar syntax to what you provide. So I am good to go.