0

Given the following sample table:

C1     C2      C3
=================
A      21      S
A      22      S
A      23      S
A      24      T
B      25      S
B      26      S

How can I write an SQL query to give the following output:

COL1   COL2       COL3
======================
A      21,22,23      S
A      24            T
B      25,26         S

For all rows in the input table where C1 and C3 are the same, I want one row in the output table that has all the C2 values concatenated, comma separated.

I'm working with an Oracle database.

Rob Thomas
  • 686
  • 7
  • 17
  • 1
    http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php – Marc B May 10 '12 at 03:40
  • Maybe this SO question will help; [Concatenate many rows into a single text string](http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string)? – Dave Anderson May 10 '12 at 03:46
  • I just realized I never came back to post the results. @MarcB pointed me in the right direction, here's my final result: SELECT C1 AS COL1, LISTAGG(C2, ',') WITHIN GROUP (ORDER BY C2) AS COL2, C3 AS COL3 FROM TBL GROUP BY C1,C3; http://www.sqlfiddle.com/#!4/73cc9/19/0 – Rob Thomas Jan 25 '19 at 22:34

3 Answers3

0

Try this if you are using oracle 11g

SELECT 
     C1, 
     LISTAGG(C2, ',') as C2,
     C3
from 
     tbl 
GROUP BY 
     C1,C1
bitoshi.n
  • 2,278
  • 1
  • 16
  • 16
0

This will be a database specific answer, so please what db you are using. Mysql is popular, so here is the Mysql5 answer:

select col1, group_concat(col2 SEPARATOR ','), col3 from tbl group by col1,col3;
Victor Bruno
  • 1,033
  • 7
  • 12
-1

In MySQL, you can use Group_Concat. For example:

SELECT GROUP_CONCAT(Language) As Languages FROM CountryLanguage WHERE CountryCode = 'THA';

devXen
  • 3,013
  • 3
  • 35
  • 44