0

I have a set of tables in a mssql 2012 database.

enter image description here

As you can see there can be multiple appsys records for one app record. And a many to many relationship between appsys records and dev language records.

I need to create a query (stored procedure not allowed in this given scenario) where I can return two columns one which is the app from the tbl_apps table and then a column that is the concatenated, comma separated values of every dev language associated with every appsys record associated with the app record.

Bastyon
  • 1,571
  • 4
  • 21
  • 28
  • 2
    Possible duplicate of [Simulating group\_concat MySQL function in SQL Server?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server) – Sean Lange Feb 23 '16 at 20:30
  • As noted below your link does point at a good solution. Unfortunately for me that thread wasn't showing up on any of my search results. Thanks for helping to point me at a good result. – Bastyon Feb 24 '16 at 18:22

1 Answers1

1

You could try adapting the code below to see if it meets your needs. You may wish to alias the tables, however I thought I'd let you come up sensible names.

SELECT DISTINCT
      app
    , STUFF(
       (SELECT ',' + dev_language
        FROM 
                tbl_apps a
                    INNER JOIN tbl_appsys ON a.app_pk = tbl_appsys.app_pk
                    INNER JOIN tbl_appsys_dev_language  ON tbl_appsys.appsys_pk = tbl_appsys_dev_language.appsys_pk
                    INNER JOIN tbl_dev_language ON tbl_appsys_dev_language.dev_language_pk = tbl_dev_language.dev_language_pk
        WHERE a.app = tbl_apps.app
        FOR XML PATH(''))
    ,1,1,'')
FROM
     tbl_apps 
        INNER JOIN tbl_appsys ON tbl_apps.app_pk = tbl_appsys.app_pk
        INNER JOIN tbl_appsys_dev_language  ON tbl_appsys.appsys_pk = tbl_appsys_dev_language.appsys_pk
        INNER JOIN tbl_dev_language ON tbl_appsys_dev_language.dev_language_pk = tbl_dev_language.dev_language_pk

Coincidently, the answer using the same method as this is also the most upvoted answer on the thread Sean linked to in the comments

Simon Ridd
  • 116
  • 5
  • You rock man. That is almost perfect. The one change I made was to make that inner select statement distinct to filter out duplicate results. Fantastic!!! – Bastyon Feb 24 '16 at 18:18