Zombraz Zombraz - 1 year ago 65
SQL Question

Concatenate column values

I'm trying to concatenate the values of a column, but the values must be formatted to another string.

Here's my table:

C1 C2 c3 c4
--------- --------- ------ --------
ID1 28-OCT-16 11.59.00 (null) 04-OCT-16 08.48.00
ID2 (null) 05-OCT-16 02.55.00 (null)
ID3 (null) 10-OCT-16 04.32.00 21-OCT-16 02.25.00
ID4 10-OCT-16 04.32.00 18-OCT-16 08.52.00 18-OCT-16 08.32.00
ID5 10-OCT-16 04.32.00 (null) (null)


I've accomplished to format the table to match the value I need.

select
c1 T_ID,
case when c2 is not null then 'Plane' end PLANE,
case when c3 is not null then 'BUS' end BUS,
case when c4 is not null then 'Hotel' end HOTEL
from table1
order by 1;




T_ID PLANE BUS HOTEL
--------- --------- ------ --------
ID1 Plane (null) Hotel
ID2 (null) BUS (null)
ID3 (null) BUS Hotel
ID4 Plane BUS Hotel
ID5 Plane (null) (null)


And i'm trying to do the following

T_ID SERVICE
--------- ---------
ID1 Plane+Hotel
ID2 BUS
ID3 BUS+Hotel
ID4 Plane+BUS+Hotel
ID5 Plane


I've tried a couple concatenation functions, but can't find the result I'm looking for.

Answer Source

You can basically do:

select c1 T_ID,
       substr( (case when c2 is not null then '+Plane' end) ||
               (case when c3 is not null then '+BUS' end) ||
               (case when c4 is not null then '+Hotel' end)
               2)
from table1 
order by 1;

This basically implements the function concat_ws() by putting the separator at the beginning of each component in the string. The outer substr() removes the first character.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download