query that groups cell data based on attribute

Question

What would I need to look into form making a query on a table that looks like this:

+---+---+---+
| A | B | C |
+---+---+---+
| 1 | x | 2 |
+---+---+---+
| 1 | x | 3 |
+---+---+---+
| 1 | x | 4 |
+---+---+---+
| 2 | y | 5 |
+---+---+---+
| 2 | y | 6 |
+---+---+---+

Into results that looks like this:

+---+---+-------+
| A | B | C     |
+---+---+-------+
| 1 | x | 2 3 4 |
+---+---+-------+
| 2 | y | 5 6   |
+---+---+-------+

The rows that have similar values from attribute A are grouped into one column though the different values from C are merged together.

Please let me know if you can help. Thanks

Answer 1

You can simply do this:

SELECT A, B, C = 
(SELECT ' ' + C
       FROM #yt b 
       WHERE b.A = a.A and b.B = a.B 
      FOR XML PATH(''))
FROM #yt a
GROUP BY A, B

I used this:

CREATE TABLE #yt
    ([A] int, [B] varchar(25), [C] int)
;

INSERT INTO #yt
    ([A], [B], [C])
VALUES
    (1, 'x', 2),
    (1, 'x', 3),
    (1, 'x', 4),
    (2, 'y', 5),
    (2, 'y', 6);

Answer 2

You did not mention about what database you are using but for PostgreSQL you can use array_agg function with group by like below:

SELECT "A", "B", array_agg("C") AS C
FROM Table1
GROUP BY 1,2;

Check solution for PostgreSQL in SQLFIDDLE

Or for mysql you can use group_concat like below:

SELECT A, B, group_concat(C) AS C
FROM Table1
GROUP BY 1,2;

Check solution for MySQL in SQLFIDDLE

Hope it helps.