Need help in grouping rows

Question

Possible Duplicate:
How can I combine multiple rows into a comma-delimited list in Oracle?

I have a table that stores data in the following manner-

60333,120102,20120502,010000,1,2

60333,120102,20120502,020000,3,4 

60333,120102,20120502,030000,5,6 

60333,120102,20120502,040000,7,8 

61663,120103,20120502,010000,9,10 

61663,120103,20120502,020000,11,12 

61663,120103,20120502,030000,13,14 

61663,120103,20120502,040000,15,16 

60333,120102,20120503,010000,17,18 

60333,120102,20120503,020000,19,20 

60333,120102,20120503,030000,21,22 

60333,120102,20120503,040000,23,24

I am expected to show the output like below:

60333,120102,20120502,1,2,3,4,5,6,7,8

60333,120102,20120503,17,18,19,20,21,22,23,24

61663,120103,20120502,,9,10,11,12,13,14 ,15,16

For a unique 60333,120102 and Date all the data is required to be shown in one line instead of 4.

How do you intend to do that? (eg. Sum the values of the other columns or show them separated by commas?) What are the names of the columns and tables and finally what have you tried so far? — aF., May 04 '12 at 09:30
This is actually a very common question. Th thread I identified has a Possible Dup has a variety of different solutions, depending on which version of the database you're using and other things. — APC, May 04 '12 at 09:44
Yep this indeed has many [duplicates](http://bit.ly/JIa19c). I just kept my answer(can be used Oracle version 11 only) here to highlight the simplicity if functionalities comes out of the box. Oracle should have gone with the same name as MySQL's `GROUP_CONCAT`, but that is infringing; infringing their own product ツ — Michael Buen, May 07 '12 at 00:05

score 1 · Answer 1 · answered May 06 '12 at 23:33

1

Try this:

select a, b, c, listagg(e || ',' || f, ',') WITHIN GROUP (ORDER BY e) as x
from tbl
group by a, b, c

Live test: http://www.sqlfiddle.com/#!4/40a4b/13

answered May 06 '12 at 23:33

Michael Buen

38,643
9
94
118

arturro · Answer 2 · 2012-05-11T21:14:22.383

If we assume that:

The 4th column can take only 4 values (010000,020000,030000,040000) and
you want to have always 11 columns in the output

then you need the "pivot query", which goes like this:

select c1, c2, c3, 
    max(decode(c4, '010000', c5)) as c5for010000,
    max(decode(c4, '010000', c6)) as c6for010000,
    max(decode(c4, '020000', c5)) as c5for020000,
    max(decode(c4, '020000', c6)) as c6for020000,
    max(decode(c4, '030000', c5)) as c5for030000,
    max(decode(c4, '030000', c6)) as c6for030000,
    max(decode(c4, '040000', c5)) as c5for040000,
    max(decode(c4, '040000', c6)) as c6for040000
my_table
group by c1, c2, c3;

Need help in grouping rows

2 Answers2