Is it possible to write a T-SQL query that does this?

Question

I have tables like

--        REGIONS  
-- ==========================
--   id |       name    
-- ==========================
--   1  |  'Western Europe'
--   2  |  'United States'
--   3  |  'United Kingdom'
--   4  |  'Middle East and Africa'
--   5  |  'Latin America'
--   6  |  'Japan'
--   7  |  'India'
--   8  |  'Germany'
--   9  |  'Greater China'
--  10  |  'France'
--  11  |  'Central and Eastern Europe'
--  12  |  'Canada'
--  13  |  'Asia Pacific'

and

--                                              Partners 
-- ==============================================================================================================
--     id  | first_name |  last_name  |    org_name           |              email                |   region_id  
-- ==============================================================================================================
--      1  |  'Dick'    |  'Cheney'   |  'Haliburton'         | 'DCheney@Haliburton.org'          |       2
--      2  | 'Warren'   |  'Buffet'   |  'Berkshire Hathaway' | 'WarrenBridgemaster@bershire.org' |       2

and a procedure that gets the partner's an their associated region names like

CREATE PROCEDURE GetPartnersWithRegion
AS
    SELECT Partners.id, 
           Partners.first_name, 
           Partners.last_name, 
           Partners.org_name, 
           Partners.email, 
           Regions.name
    FROM Partners INNER JOIN Regions
           ON Partners.region_id=Regions.id
    ORDER BY org_name ASC

Now, as an extra requirement of my development project, I have a table that makes a one-to-many relationship of partners with regions like

--           Destinations 
-- ================================
--   id | partner_id | region_id 
-- ================================
--   1  |     1      |    1
--   2  |     1      |    2

if Dick Cheney is associated with the regions Western Europe and United States. What I'd like to do, if possible, is modify GetPartnersWithRegion so that it has these destinations separated by a comma, like

--                                                                   Results
-- =====================================================================================================================================================
--     id  | first_name |  last_name  |    org_name           |              email                |     region_name   |           destinations 
-- =====================================================================================================================================================
--      1  |  'Dick'    |  'Cheney'   |  'Haliburton'         | 'DCheney@Haliburton.org'          |  'United States'  | 'Western Europe, United States'
--      2  | 'Warren'   |  'Buffet'   |  'Berkshire Hathaway' | 'WarrenBridgemaster@bershire.org' |  'United States'  | 'Latin America, 'Asia Pacific'

Is this possible? Or is there a different way I should be doing this in the first place?

yeah. http://stackoverflow.com/a/451441/1291428 – Sebas Feb 18 '16 at 01:05 — Sebas, Feb 18 '16 at 01:05
note: `Destinations` is not one to many, it's many to many – Sebas Feb 18 '16 at 01:05 — Sebas, Feb 18 '16 at 01:05

score 2 · Accepted Answer · answered Feb 18 '16 at 01:08

We need to use FOR XML query to construct a comma separated list, like:

SELECT Partners.id, 
           Partners.first_name, 
           Partners.last_name, 
           Partners.org_name, 
           Partners.email, 
           Regions.name,
           STUFF((SELECT ',' + r.name FROM regions r INNER JOIN destinations d ON (r.region_id = d.region_id AND d.partner_id = p.partner_id) FOR XML PATH('')),1,1,'') AS [Destinations]
    FROM Partners p

score 0 · Answer 2 · answered Feb 18 '16 at 01:09

0

The best trick for concatenating row values into a comma-delimited string is using the FOR XML PATH() functionality. Lot's of examples out there, but I found this pretty clear:

http://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/

answered Feb 18 '16 at 01:09

Steve Murchie

1

Is it possible to write a T-SQL query that does this?

2 Answers2