I was asked this question in an Interview and couldn't crack it.
I have a Employee table with following values
Id Name Sal
1 Sid 1000
2 Jon 800
3 Ram 600
I want the output to be displayed as follows:
1 2 3
Sid Jon Ram
1000 800 600
i.e rows to columns.
I used case/decode for the same. But he required different answer as there might be many more columns and he didn't want to use decode for all.
I searched on the net for Pivot and some other functions but wasn't able to make query which could provide me this output. Is it possible to get output in the above mentioned format? If yes can I get the query for this.
Thanks in Advance.
You're switching the columns and rows over. One way to do this is with pivot AND unpivot.
To do this, first you add a calculated row_number to "pivot by". Then list all your columns in the pivot aggregates:
drop table t purge;
create table t (id int, name varchar2(3), sal int);
insert into t values (1, 'Sid', 1000);
insert into t values (2, 'Jon', 800);
insert into t values (3, 'Ram', 600);
with pivoted as (
select * from (
select row_number() over (order by id) rn, t.* from t
)
pivot (min(to_char(id)) c1, min(to_char(name)) c2, min(to_char(sal)) c3
for rn in (1, 2, 3)
)
)
select * from pivoted;
1_C1 1_C2 1_C3 2_C1 2_C2 2_C3 3_C1 3_C2 3_C3
1 Sid 1000 2 Jon 800 3 Ram 600
Note the to_char(). This is important. The final output will contain both numbers and strings in a single column. So you need to normalize to one type (strings!).
Now you have everything in one line you can unpivot the values.
"But there's multiple columns to unpivot!" you say.
Not a problem. Just like we pivoted multiple values, you can unpivot multiple columns!
Specify the names of the three columns you want inside parentheses. Then provide the values for each row in parenthesis again.
For example, the values for the first row come from columns 1_C1, 2_C1 & 3_C1 in the pivoted data. So you want ("1_C1", "2_C1", "3_C1").
Put it all together and you get:
with pivoted as (
select * from (
select row_number() over (order by id) rn, t.* from t
)
pivot (min(to_char(id)) c1, min(to_char(name)) c2, min(to_char(sal)) c3
for rn in (1, 2, 3)
)
)
select c1, c2, c3 from (
select * from pivoted
)
unpivot ((c1, c2, c3) for val in (
("1_C1", "2_C1", "3_C1"),
("1_C2", "2_C2", "3_C2"),
("1_C3", "2_C3", "3_C3")
));
C1 C2 C3
1 2 3
Sid Jon Ram
1000 800 600
Note: You've said you're on 10.1. Pivot is only available in 11g. If you need a 10g solution look at:
https://asktom.oracle.com/pls/apex/f?p=100:11:::::P11_QUESTION_ID:9470921600346261156