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.

Community · Accepted Answer · 2017-05-23 11:45:17Z

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, 2017 at 11:45

CommunityBot

11 silver badge

answered Oct 5, 2015 at 9:24

Hotdin Gurning

1,8193 gold badges16 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zacurned Ace Over a year ago

This give me GP * 5 / 100 + instead of GP * 5 / 100. I want to ignore the last op for each groups.

Zacurned Ace Over a year ago

I added substr to remove the last op. Thanks.

Martin Schapendonk · Accepted Answer · 2015-10-05 09:29:49Z

1

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.

answered Oct 5, 2015 at 9:29

Martin Schapendonk

13.6k3 gold badges22 silver badges26 bronze badges

Comments

Lukasz Szozda · Accepted Answer · 2015-10-05 09:31:34Z

1

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)

answered Oct 5, 2015 at 9:31

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Comments

San · Accepted Answer · 2015-10-05 09:36:55Z

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

Collectives™ on Stack Overflow

Concatenate multiple columns from multiple rows in Oracle

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related