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?

duckduckgo.com/?q=sql+server+group_concat&t=ffsb&ia=web

jarlh
– jarlh

2017-01-03 11:54:59 +00:00
Commented Jan 3, 2017 at 11:54 — jarlh
– jarlh, Commented Jan 3, 2017 at 11:54
Take a look: stackoverflow.com/questions/17591490/…

McNets
– McNets

2017-01-03 11:57:49 +00:00
Commented Jan 3, 2017 at 11:57 — McNets
– McNets, Commented Jan 3, 2017 at 11:57

Gordon Linoff · Accepted Answer · 2017-01-03 11:58:20Z

1

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;

answered Jan 3, 2017 at 11:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

OmniOwl Over a year ago

Yeah at most a flight should never have more than 3 providers (and 3 providers would be super rare)

Collectives™ on Stack Overflow

Merging Data in Column when Duplicate in SQL Query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related