CODEWITHSUNDEEP
oracle-databaseoracle11goracle10g
susmitha vangala
susmitha vangala

Reputation: 37

how to concatenate 2 rows which have null values using listagg() function in oacle

1234    NULL
1234    HELLO EVERYBODY
7895    NULL

I want the result to be like 

1234  hello everybody,null
7895  null

I have tried using listagg(nvl(col, 'NULL')) in oracle,but it din't work. Please help me with this.

Upvotes: 0

Views: 1846

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49092

SQL> WITH DATA AS(
  2  SELECT 1234 ID,   NULL txt FROM dual UNION ALL
  3  SELECT 1234 ID,   'HELLO EVERYBODY' txt FROM dual UNION ALL
  4  SELECT 7895 ID,   NULL txt FROM dual)
  5  SELECT ID, LISTAGG(nvl(txt,'NULL'), ',')
  6            WITHIN GROUP (ORDER BY ID) list_agg
  7  FROM DATA
  8  GROUP BY ID
  9  /

                  ID LIST_AGG
-------------------- ------------------------------
                1234 HELLO EVERYBODY,NULL
                7895 NULL

SQL>

EDIT The WITH clause is to build the sample table for just demonstration. In your database you need to just rename the DATA with your table name and just execute the select listagg.. query.

So, your final query would look like,

SELECT column_1, 
       LISTAGG(nvl(column_2,'NULL'), ',')
       WITHIN GROUP (ORDER BY column_1) list_agg
FROM table_name
GROUP BY column_1
/

Upvotes: 1

Related Questions