Concatenate multiple columns from multiple rows in Oracle

Question

I searched this in stackoverflow and found Concatenate multiple columns and also Concatenate multiple rows. But what I need is combine them both.
I have table named komponen :

id   urut   tipe   c_string   c_number    op
---------------------------------------------
A1    1      S      GP         NULL        *
A1    2      N      NULL       5           /
A1    3      N      NULL       100         +  //Ignore the last op for each groups.
A2    1      S      GP         NULL        -
A2    2      N      NULL       1000        /  //Ignore the last op for each groups.

Desired result :

id     concat_result
------------------------
A1     GP * 5  /  100
A2     GP - 1000

This might be use LISTAGG and GROUP BY method. But I don't have any clue how to do this and achieve the desired result. Please help.

score 1 · Accepted Answer · edited May 23 '17 at 11:45

1

This with some modification from here . Added || (concatenate) in LISTAGG

SELECT ID,
   LISTAGG (
      CASE
      WHEN TIPE = 'v' THEN
         C_STRING
      ELSE
         TO_CHAR (C_NUMBER)
      END || OP,
      ' '
   ) WITHIN GROUP (ORDER BY URUT) AS CONCAT_RESULT
FROM KOMPONEN
GROUP BY ID;

edited May 23 '17 at 11:45

Community

1
1

answered Oct 05 '15 at 09:24

Hotdin Gurning

1,821
3
15
24

This give me `GP * 5 / 100 +` instead of `GP * 5 / 100`. I want to ignore the last op for each groups. – Zacurned Ace Oct 05 '15 at 09:36
I added substr to remove the last op. Thanks. – Zacurned Ace Oct 05 '15 at 09:57

score 1 · Answer 2 · answered Oct 05 '15 at 09:29

Your hunch is correct. In this solution, I assume that c_string and c_number are mutually exclusive, i.e. exactly one will be not null.

with t as (
  select id, listagg(nvl(c_string, c_number)||' '||op, ' ') within group (order by urut) result
  from komponen
  group by id
)
select id, substr(result, 1, length(result)-2)
from t;

The combination of with and substr() is to remove the last operator.

score 1 · Answer 3 · answered Oct 05 '15 at 09:31

You can use:

SqlFiddleDemo

WITH cte AS
(
   SELECT  
      id,
      LISTAGG(string , '') WITHIN GROUP (ORDER BY urut) AS concat_result
   FROM(SELECT id, urut,
        NVL(c_string, '') || ' ' || NVL(c_number, '') || ' ' ||  NVL(op, '')  AS string
        FROM komponen)
   GROUP BY id
)
SELECT
  id, 
  SUBSTR(concat_result,1,LENGTH(concat_result)-1) AS concat_result
FROM cte

How it works:

Concatenate with row NVL(c_string, '') || ' ' || NVL(c_number, '') || ' ' || NVL(op, '')
Concatenate with multiple rows LISTAGG
Remove last character SUBSTR(concat_result,1,LENGTH(concat_result)-1)

score 1 · Answer 4 · answered Oct 05 '15 at 09:36

One way is by using row_number analytical function and listaggr

SQL> WITH table_("ID",   urut,   tipe,   c_string,   c_number,    op) AS
  2  (SELECT 'A1', 1, 'S', 'GP', NULL, '*' from dual union all
  3   SELECT 'A1', 2, 'N', NULL, '5', '/' from dual union all
  4   SELECT 'A1', 3, 'N', NULL, '100', '+' from dual union all
  5   SELECT 'A2', 1, 'S', 'GP', NULL, '-' from dual union all
  6   SELECT 'A2', 2, 'N', NULL, '1000', '/' from dual),
  7   -------
  8   -- End if Data preparation
  9   -------
 10  table2_ AS (SELECT t.*, row_number() OVER (partition BY "ID" ORDER BY URUT DESC) AS rn
 11    FROM table_ t)
 12  SELECT "ID",
 13         listagg(coalesce(c_string, c_number) || ' ' || CASE
 14                     WHEN rn = 1 THEN
 15                      NULL
 16                     ELSE
 17                      op
 18                 END,
 19                 ' ') within GROUP(ORDER BY urut) AS EXPR
 20    FROM table2_
 21   GROUP BY "ID"
 22  /

OUTPUT

ID EXPR
-- --------------------------------------------------------------------------------
A1 GP * 5 / 100
A2 GP - 1000

Concatenate multiple columns from multiple rows in Oracle

4 Answers4