SQL - Group fields into a column

Question

I have a query which gets all public transport subscriptions. A subscription can have multiple related companies. So I wanted to do with my query is to get all the subscriptions and instead of creating a row each time I have a related company, the companies should by group into one column. Is it possible to do that?

Here's the query :

SELECT pts.Id_PublicTransportSubscription, 
       pts.Amount, 
       ptc.Name
FROM bm_PublicTransportSubscriptions AS pts 
  INNER JOIN bm_PublicTransportSubscriptionByCompany AS ptsbc 
  ON pts.Id_PublicTransportSubscription = ptsbc.Id_PublicTransportSubscription 
  INNER JOIN bm_PublicTransportCompanies AS ptc 
  ON ptsbc.Id_PublicTransportCompany = ptc.Id_PublicTransportCompany

I'm using SQL Server 2008.

Please take a look at [this SO answer](http://stackoverflow.com/a/273330/1231866). — Nikola Markovinović, May 06 '13 at 08:35

score 0 · Answer 1 · answered May 06 '13 at 08:20

0

You can use the GROUP_CONCAT aggregate function.

answered May 06 '13 at 08:20

linepogl

9,147
4
34
45

Sorry, I forgot to mention it but I'm using SQL Server 2008. – Traffy May 06 '13 at 08:28

SQL - Group fields into a column

1 Answers1