Merging Data in Column when Duplicate in SQL Query

Question

I have this query here at work:

SELECT flightdata.FLIGHTID,
       flightdata.FLIGHTDATE,
       comments.COMMENT,
       comments.SURVEYID,
       providercodes.PROVIDERNAME
FROM dbo.AnswersComment AS comments
     INNER JOIN dbo.Surveys AS surveys ON comments.SURVEYID = surveys.ID
     INNER JOIN dbo.FlightData AS flightdata ON surveys.FLIGHTDATAID = flightdata.ID
     INNER JOIN dbo.FlightProviderData AS providerdata ON providerdata.FLIGHTDATAID = flightdata.ID
     INNER JOIN dbo.ProviderCode AS providercodes ON providercodes.ID = providerdata.PROVIDERCODEID
ORDER BY FLIGHTDATE ASC;

And it produces data just fine like this:

+--------+------------+--------------+----------+--------------+
| FLIGHT | FLIGHTDATE |   COMMENT    | SUVERYID | PROVIDERNAME |
+--------+------------+--------------+----------+--------------+
| ID0000 | 2016-08-04 | some comment |   123456 | ABC          |
+--------+------------+--------------+----------+--------------+

The Query is used to get Comments that customers leave us after they have used one of our flights. The problem I want to solve occurs when two Survey IDs are the same. Then I get duplicate rows where all the data is the same, except for the Provider (as one flight can have more than one provider at a time):

+--------+------------+--------------+----------+--------------+
| FLIGHT | FLIGHTDATE |   COMMENT    | SUVERYID | PROVIDERNAME |
+--------+------------+--------------+----------+--------------+
| ID0000 | 2016-08-04 | some comment |   123456 | ABC          |
| ID0000 | 2016-08-04 | some comment |   123456 | CBA          |
+--------+------------+--------------+----------+--------------+

What I would like to happen when two Survey IDs are the same, is this:

+--------+------------+--------------+----------+--------------+
| FLIGHT | FLIGHTDATE |   COMMENT    | SUVERYID | PROVIDERNAME |
+--------+------------+--------------+----------+--------------+
| ID0000 | 2016-08-04 | some comment |   123456 | ABC,CBA      |
+--------+------------+--------------+----------+--------------+

So that the Provider Names are merged into one column during the query, separated by a comma. How would I accomplish this, modifying the current Query that I got?

https://duckduckgo.com/?q=sql+server+group_concat&t=ffsb&ia=web — jarlh, Jan 03 '17 at 11:54
Take a look: http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server — McNets, Jan 03 '17 at 11:57

score 1 · Accepted Answer · answered Jan 03 '17 at 11:58

Aggregate string concatenation is a pain in SQL Server -- needing to use a subquery with for xml path. If you know that you have only 2 or 3 providers, you can use another trick:

with t as (<your query here>)
select FLIGHT, FLIGHTDATE, COMMENT, SURVEYID,
       (max(case when seqnum = 1 then providername else '' end) +
        max(case when seqnum = 2 then ',' + providername else '' end) +
        max(case when seqnum = 3 then ',' + providername else '' end)
       ) as providernames 
from (select t.*,
             row_number() over (partition by FLIGHT, FLIGHTDATE, COMMENT, SURVEYID order by providername) as seqnum
      from t
     ) t
group by FLIGHT, FLIGHTDATE, COMMENT, SURVERID;

Yeah at most a flight should never have more than 3 providers (and 3 providers would be super rare) — OmniOwl, Jan 03 '17 at 12:07

Merging Data in Column when Duplicate in SQL Query

1 Answers1