CODEWITHSUNDEEP
sqloracle-database
babayaro
babayaro

Reputation: 531

Oracle SQL union all in one row

I have two query and get result in two row. How get result below union all in one row?

Select 'STACK' as NAME, 'Y' as DAY_1, 'Y' as DAY_2, null as DAY_3, null as DAY_4 from dual
union all
Select 'STACK' as NAME, null as DAY_1, null as DAY_2, 'Y' as DAY_3, 'Y' as DAY_4 from dual

Upvotes: 1

Views: 1122

Answers (3)

Marmite Bomber
Marmite Bomber

Reputation: 21043

Oracle added starting in 12.2 a row_limiting_clause - simple add fetch next 1 rows only to the query if you need to return only one row.

Select 'STACK' as NAME, 'Y' as DAY_1, 'Y' as DAY_2, null as DAY_3, null as DAY_4 from dual
union all
Select 'STACK' as NAME, null as DAY_1, null as DAY_2, 'Y' as DAY_3, 'Y' as DAY_4 from dual
fetch next 1 rows only;

Note, that this is the simplest possible solution to return one arbitrary row. That matches you requirements a you did not mention if you prefere some order of the selection or even if you need to aggregate the information in one row.

Upvotes: 1

Jon Armstrong
Jon Armstrong

Reputation: 4694

WITH xrows AS (
        Select 'STACK' as NAME, 'Y' as DAY_1, 'Y' as DAY_2, null as DAY_3, null as DAY_4 from dual
         union all
        Select 'STACK' as NAME, null as DAY_1, null as DAY_2, 'Y' as DAY_3, 'Y' as DAY_4 from dual
     )
SELECT name
     , MIN(day_1) AS day_1
     , MIN(day_2) AS day_2
     , MIN(day_3) AS day_3
     , MIN(day_4) AS day_4
  FROM xrows
 GROUP BY name
;

Full test case

Result:

NAME    DAY_1   DAY_2   DAY_3   DAY_4
STACK   Y       Y       Y       Y

Upvotes: 3

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

My crystal ball says it should be:

select
  NAME
, max(DAY_1) as DAY_1
, max(DAY_2) as DAY_2
, max(DAY_3) as DAY_3
, max(DAY_4) as DAY_4
from ...

Upvotes: 1

Related Questions