Post reply

Return query with concatenating values.

  • 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

  • November 22, 2013 at 1:55 pm

    #1668533

    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]

  • November 22, 2013 at 2:00 pm

    #1668535

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

    http://qa.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

  • November 22, 2013 at 2:28 pm

    #1668541

    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.

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

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