Home » SQL & PL/SQL » SQL & PL/SQL » Concatenation of nulls not happening. (10g)
Concatenation of nulls not happening. [message #361864] Fri, 28 November 2008 05:12 Go to next message
J1357
Messages: 33
Registered: November 2008
Member
Gurus,

I'm having a table which has column_1,column_2,upto column_5.
These may or may not have any values in them.(Null values may exists)
I want a formatted output which will be
1)Concatenated of values (if data exists in the columns)
2)else concatenation of the column will not happen
Column_1 Column_2 Column_3 Column_4 Column_5 Desired O/p
-------- -------- -------- -------- -------- -----------
12 8 7 24 12,8,7,24
4 8 4,8
1 8 74 1,8,74
1 7 6 1,7,6

select decode(nvl2(Column_1,Column_1,null),null,to_char(0),Column_1)||
decode(nvl2(Column_2,Column_2,null),null,to_char(0),Column_2)||
decode(nvl2(Column_3,Column_3,null),null,to_char(0),Column_3)||
decode(nvl2(Column_4,Column_4,null),null,to_char(0),Column_4)||
decode(nvl2(Column_5,Column_5,null),null,to_char(0),Column_5)||
decode(nvl2(Column_6,Column_6,null),null,to_char(0),Column_6)||
decode(nvl2(Column_7,Column_7,null),null,to_char(0),Column_7) as col1 from Table
Col1
---------
1280724
0408000
1087400
01706

Also, I need ',' in between 2 fields .

Kindly modify the query which i posted.
Re: Concatenation of nulls not happening. [message #361865 is a reply to message #361864] Fri, 28 November 2008 05:16 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Previous Topic: QUERY SHOULD RETURN 1 HOUR
Next Topic: tutorial on stored procedure
Goto Forum:
  


Current Time: Sat Dec 03 22:10:04 CST 2016

Total time taken to generate the page: 0.11109 seconds