#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    1
    Rep Power
    0

    Question Combine field data from several rows into one row


    I've seen several posts for this type of issue - but have not seen any solutions.

    I am a report developer (new to SQL Svr). I am using SQL Server 2008 r2. I have three tables. The main table is the Provider table. It connects to the Prov_Target_Pops table on the Provider table primary key. The Prov_Target_Pops table connects to the Target_Population table which lists the descriptions of the target pops.

    My result set displays a row for each provider for each target pop. I want the result set to show one row per provider with the target pops combined (separated by commas) in one field.

    My query is:
    SELECT DISTINCT
    tbl_Providers.PROV_ID, tbl_Providers.PROV_NAME, tbl_Providers.ADDRESS1, tbl_Providers.ADDRESS2, tbl_Providers.CITY, tbl_Providers.STATE,
    tbl_Providers.ZIP_CODE, tbl_pp_provider.phys_phone, tbl_Providers.COUNTY, tbl_Target_Population.DESCRIPTION AS PopSvd

    FROM tbl_Providers INNER JOIN
    tbl_Prov_Target_Pops ON tbl_Providers.PROV_ID =
    tbl_Prov_Target_Pops.PROV_ID INNER JOIN
    tbl_Target_Population ON
    tbl_Prov_Target_Pops.TARGET_POP_ID =
    tbl_Target_Population.TARGET_POP_ID INNER JOIN
    tbl_pp_provider ON tbl_Providers.PROV_ID =
    tbl_pp_provider.prov_id

    GROUP BY tbl_Providers.PROV_ID, tbl_Providers.PROV_NAME,
    tbl_Providers.ADDRESS1, tbl_Providers.ADDRESS2,
    tbl_Providers.STATE, tbl_Providers.ZIP_CODE,
    tbl_Providers.COUNTY,
    tbl_Target_Population.DESCRIPTION, tbl_Providers.CITY,
    tbl_pp_provider.phys_phone

    HAVING (tbl_Providers.COUNTY IN
    ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L',
    'M', 'N', 'O', 'P', 'Q', 'R', 'S'))

    ORDER BY tbl_Providers.COUNTY, tbl_Providers.CITY,
    tbl_Providers.PROV_NAME

    The sample result set is:

    Prov_ID PROV_NAME Description
    1 Provider 1 Svc 1
    1 Provider 1 Svc 2
    1 Provider 1 Svc 3
    1 Provider 1 Svc 4
    1 Provider 1 Svc 5
    1 Provider 1 Svc 6
    3 Provider 3 Svc 1
    3 Provider 3 Svc 2
    3 Provider 3 Svc 3
    3 Provider 3 Svc 4

    The desired result set is:

    Prov_ID PROV_NAME Description
    1 Provider 1 Svc 1, Svc 2, Svc 3, Svc 4,
    Svc 5, Svc 6
    3 Provider 3 Svc 1, Svc 2, Svc 3, Svc 4

    Can someone please help me with this?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,260
    Rep Power
    4279
    Originally Posted by Ju1iaWhite
    I've seen several posts for this type of issue - but have not seen any solutions.
    that's because there really aren't any

    i think there is one using XML but it's really clunky and i don't know the syntax offhand

    why can't you do the concatenation in your application layer?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo