Pivot from row to column - Oracle SQL

Question

I have a table:

Table1

row_id     var        var_val
1          Test 1      123
1          Test 2      456
1          Test 3      789
1          Test 4      1234
2          Test 1      665t
2          Test 2      dsfs
2          Test 3      df
2          Test 4      sfd
3          Test 1      sfs
3          Test 2      sf
3          Test 3      sdfs
3          Test 4      sfsd

Here is the output:

Table2

row_id      var1        var2
1           123         456
2           665t        dsfs
3           sfs         sf

For var1 - get value where var = "Test 1"
For var2 - get value where var = "Test 2"

Is there a way to use pivot or some way of extracting the variable for each row_id from the table1 as per above?

What does column var1 and var2 contains? how it is being calculated? — Rima
– Rima, Commented Apr 30, 2019 at 11:17

Gordon Linoff · Accepted Answer · 2019-04-30 11:19:04Z

2

You can use conditional aggregation or a join:

select t11.row_id, t11.var, t12.var
from table1 t11 join
     table1 t12
     on t11.row_id = t12.row_id and
        t11.var = 'Test 1' and
        t12.var = 'Test 2'

answered Apr 30, 2019 at 11:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ponder Stibbons · Accepted Answer · 2019-04-30 11:26:48Z

0

Is there a way to use pivot...?

Sure:

select * 
  from table1
  pivot (max(var_val) for var in ('Test 1' var1, 'Test 2' var2))

^demo

answered Apr 30, 2019 at 11:26

Ponder Stibbons

14.9k2 gold badges23 silver badges24 bronze badges

Comments

Barbaros Özhan · Accepted Answer · 2019-04-30 11:44:21Z

You can use correlated subqueries and row_number() window analytic function together

with table1(row_id, var, var_val) as
(
 select 1,'Test 1','123'  from dual union all
 select 1,'Test 2','456'  from dual union all
 select 1,'Test 3','789'  from dual union all
 select 1,'Test 4','1234' from dual union all
 select 2,'Test 1','665t' from dual union all
 select 2,'Test 2','dsfs' from dual union all
 select 2,'Test 3','df'   from dual union all
 select 2,'Test 4','sfd'  from dual union all
 select 3,'Test 1','sfs'  from dual union all
 select 3,'Test 2','sf'   from dual union all
 select 3,'Test 3','sdfs' from dual union all
 select 3,'Test 4','sfsd' from dual
), t2 as
(
select t.*, row_number() over (partition by var order by row_id) as rn
  from table1 t
)
select distinct row_id,
                (select var_val
                   from table1 t2
                  where t2.var = 'Test 1'
                    and t2.row_id = rn) as var1,
                (select var_val
                   from table1 t2
                  where t2.var = 'Test 2'
                    and t2.row_id = rn) as var2
  from t2
 order by row_id

 ROW_ID VAR1    VAR2
 ------ ----    ----
 1      123     456
 2      665t    dsfs
 3      sfs     sf

Demo

Collectives™ on Stack Overflow

Pivot from row to column - Oracle SQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related