CODEWITHSUNDEEP
sqloracle-database
Lee Theobald
Lee Theobald

Reputation: 8587

SQL Query Where Columns List Keys With/Without Boolean

Imagine I have a table like below:

|-----|-------|
| KEY | VALUE |
|-----|-------|
| A   | 0     |
| B   | 1     |
| C   | 0     |
|-----|-------|

What I'd like to do is to perform a SQL query that would give me a table like below:

|---------------|------------|
| Without Value | With Value |
|---------------|------------|
| A             | B          |
| C             |            |
|---------------|------------|

So Column A is where Active = 1 and Column B is where Active = 0. But my SQL is basic and I don't know quite how to achieve this (if it's at all possible). Anyone have any ideas?

This on an Oracle DB.

Upvotes: 1

Views: 75

Answers (3)

MT0
MT0

Reputation: 168041

You can do it without resourting to self-joins by giving each row an index using the ROW_NUMBER() analytic function and then use a PIVOT:

SELECT Without_Value, With_Value
FROM   ( SELECT t.*,
                ROW_NUMBER() OVER ( PARTITION BY Value ORDER BY ROWNUM ) AS idx
         FROM   table_name t )
PIVOT  ( MAX( Key ) FOR Value IN ( 0 AS Without_Value, 1 AS With_Value ) );

Output:

WITHOUT_VALUE WITH_VALUE
------------- ----------
A             B
C

Upvotes: 1

Adam
Adam

Reputation: 231

This should help

select case when value=0 then key else null end as "Without Value",case when value=1 then key else null end as "With Value" from key_value

Upvotes: 0

user330315
user330315

Reputation:

You can do that with a full outer join between the list of keys with a value and those without:

select no_val.key as "Without value", with_val.key as "With Value"
from (
    select key, row_number() over (order by key) as rn
    from kv
    where value = 0
 ) no_val
 full outer join (
    select key, row_number() over (order by key) as rn
    from kv
    where value = 1
 ) with_val 
 on no_val.rn = with_val.rn;

Online example: http://rextester.com/RMKRRA65292

Upvotes: 4

Related Questions