Concatenation of 2 values from same column using oracle SQL

Question

I'm trying to concatenate the values of a column.

For EX:

select case when month=1 then month_name1 
when month =12 then month_name2 END
from Calendar
where month in (1,12)

results:

Jan
Dec

Target results:

Jan - Dec

tried different ways but couldn't get it. looks very simple but hard luck. Thanks in advance.

Answer 1

You can use aggregate function max as following:

select max(case when month=1 then month_name1 end)
       || ' - '||
       max(case when month=12 then month_name2 end)
from Calendar
where month in (1,12);

Cheers!!

Answer 2

I feel you would need a hack here, below query must do the magic !!

select c1.month_name1 || ' - ' || c2.month_name2 from Calendar c1, calendar c2 where c1.month = 1 and c2.month = 12 ;

Answer 3

Try this query using listagg

select listagg(case when month=1 then month_name1 
               when month =12 then month_name2 END, ' - ')
         within group (order by month)
from Calendar
where month in (1,12)