1

Till now i haven't got a need to post a query, almost everything is available. This is my first question.

I have a different requirement, I have the below table name say alarmdb

------------------INT_ID----------------------------------------------------- ALARM_NUMBER----------------------------

             101212121                               7762
             101212121                               8212
             101212121                               3423
             101313131                               7734
             101313131                               7743

Basically its kind of grouping, and pivoting. Final output can be like this.

------------------INT_ID----------------------------------------------------- ALARM_NUMBER----------------------------

             101212121                           7762,8212,3423
             101313131                               7734,7743

Kind of shrinking many cells data into one.

Can anyone please suggest.

APC
  • 144,005
  • 19
  • 170
  • 281
Chandra Sekhar
  • 9
  • 2

1 Answers1

2

Depending on your version of Oracle you have different options.

You can use LISTAGG for Oracle 11g+:

select int_id,
  listagg(alarm_number, ', ') within group (order by int_id) as alarm_number
from yourtable
group by int_id;

See SQL Fiddle with Demo

Or you can use wm_concat() for earlier versions:

select int_id,
  wm_concat(alarm_number) as alarm_number
from yourtable
group by int_id
Taryn
  • 242,637
  • 56
  • 362
  • 405
  • WM_CONCAT() is not a documented function, and so shouldn't be relied on in production code. – APC Apr 10 '13 at 05:55
  • can you please tell me how to use, wm_concat is not supported i guess, am posting the versions below. – Chandra Sekhar Apr 11 '13 at 05:07
  • Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production "CORE 10.2.0.5.0 Production" TNS for Linux: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 - Production – Chandra Sekhar Apr 11 '13 at 05:07
  • @ChandraSekhar I would suggest reviewing the following page which shows some examples on how you can do this in Oracle 10g -- http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php – Taryn Apr 11 '13 at 10:09