2

I have a table TAB1 in an Oracle database:

+----+----+----+----+----+----+
| ID | C1 | C2 | C3 | C4 | C5 |
+----+----+----+----+----+----+
|  1 |  1 |  0 |  3 |  0 |  5 |
|  2 | 11 | 22 |  0 | 44 |  0 |
+----+----+----+----+----+----+

I need a select statement that gives me the following output:

+----+-------------------------+
| id |          descr          |
+----+-------------------------+
|  1 | column1,column3,column5 |
|  2 | column1,column2,column4 |
+----+-------------------------+

...meaning I need in to concatenate names for every column (not exactly the name of the column but a different text for every column) that is not 0.

3 Answers 3

2

Something like (untested):

select
  id,
  rtrim(
    case when c1 != 0 then 'column1,' end
 || case when c2 != 0 then 'column2,' end
 || case when c3 != 0 then 'column3,' end
 || case when c4 != 0 then 'column4,' end
 || case when c5 != 0 then 'column5' end, ',') as descr
from tab1;
2

Another way:

  1. Unpivot the columns.

  2. Filter out the 0's.

  3. Group by ID and concatenate the column names.

Something like this (assuming you are using Oracle 11g):

SELECT
  ID,
  LISTAGG(Col, ',') WITHIN GROUP (ORDER BY Col) AS descr
FROM
  atable
UNPIVOT
  (
    Val FOR Col IN (C1, C2, C3, C4, C5)
  ) u
WHERE
  Val <> 0
GROUP BY
  ID
;
1

Make a concatenation of every field in the form of:

CASE WHEN columnX!=0 THEN 'textX,' ELSE '' END

This would make:

select id, trim(CASE WHEN column1!=0 THEN 'text1,' ELSE '' END||
                CASE WHEN column2!=0 THEN 'text2,' ELSE '' END||
                CASE WHEN column3!=0 THEN 'text3,' ELSE '' END||
                CASE WHEN column4!=0 THEN 'text4,' ELSE '' END||
                CASE WHEN column5!=0 THEN 'text5' ELSE '' END, ',') as descr
from   tab1;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.