Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Multiple column sql output format

Ryansun-OracleNov 28 2016 — edited Nov 28 2016

11g

  Hi gurus,

  I have a table T1 with three columns

Id    Col1       Col2     Col3

1      O             P        null

2      null          P        C

3     null          null     null

4      O             P        C

5      O            null      C

6     O              null     null

The output should be a comma separated list of col1, col2, col3

E.g

Select decode(col1,null,null,'O, ')||decode(Col2,null,null,'P, ')||decode(Col3,null,null,'C') from  T1   

The problem is that if the last of the three cols have null, then there is a redundant ' ,' So output is

O,P,

P,C

null

O,P,C

O,C

O,

The first and last row has the comma.So is there a way to format this so that the redundant comma can be eliminated.

Thank

Ryan

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 26 2016
Added on Nov 28 2016
4 comments
690 views