How to list the results from a SELECT statement into a column of another SELECT statement? [duplicate]

Question

(I'm quite sure this question is answered here somewhere, but I'm not finding it, nor being able to put together the terms for a good search. So I'm sorry in advance if this question is duplicate.)

Here's my problem:

If I have a SELECT statement like this:

SELECT a.Id, b.Id
FROM TableA as a
JOIN TableB as b on a.Id = b.Id

And I get a result like this:

+-----+------+
|a.Id | b.Id |
|  1  |  1   |
|  1  |  2   |
|  2  |  1   |
|  2  |  3   |
|  2  |  4   |
|  3  |  3   |
+-----+------+

I would like to know how can I approach the problem of having to get this:

+-----+----------+
|a.Id | b.Id     |
|  1  |  1, 2    |
|  2  |  1, 3, 4 |
|  3  |  3       |
+-----+----------+

Further info / Edit: I'm using MSSQL. And yes, this question is duplicated: (Does T-SQL have an aggregate function to concatenate strings?)

what sql server are you using? MySQL, MSSQL, Postgres?

Al W
– Al W

2016-05-24 16:02:07 +00:00
Commented May 24, 2016 at 16:02 — Al W
– Al W, Commented May 24, 2016 at 16:02

ScaisEdge · Accepted Answer · 2016-05-24 16:10:03Z

0

In mysql you can use group_concat

SELECT a.Id, group_concat(b.Id)
FROM TableA as a
JOIN TableB as b on a.Id = b.Id
group by a.id;

answered May 24, 2016 at 16:10

ScaisEdge

134k10 gold badges98 silver badges111 bronze badges

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

1 Comment

Mari Faleiros Over a year ago

Oh, thanks for the suggestion. I'm using MSSQL but I'll look for something similar

Collectives™ on Stack Overflow

How to list the results from a SELECT statement into a column of another SELECT statement? [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related