Google
Information Storage and Retrieval: Club the columns!!! (SQL Puzzle 17)

Pages

Thursday, August 21, 2008

Club the columns!!! (SQL Puzzle 17)

You have 2 columns in a table :

create table tab_sub_code (Subject_Codes char(3), subject_name varchar2(30));
with data as.....
insert into tab_sub_code (subject_codes, subject_name) values ('001', 'sub_1Name');
insert into tab_sub_code (subject_codes, subject_name) values ('002', 'sub_2Name');
insert into tab_sub_code (subject_codes, subject_name) values ('003', 'sub_3Name');
insert into tab_sub_code (subject_codes, subject_name) values ('004', 'sub_4Name');
insert into tab_sub_code (subject_codes, subject_name) values ('005', 'sub_5Name');
insert into tab_sub_code (subject_codes, subject_name) values ('006', 'sub_6Name');
insert into tab_sub_code (subject_codes, subject_name) values ('007', 'sub_7Name');
insert into tab_sub_code (subject_codes, subject_name) values ('008', 'sub_8Name');
Problem:
Write an SQL to fetch both columns in 1 column with values placed alternatively and separated by a NULL. The result should look like:
001
sub_1Name
NULL
002
sub_2Name
NULL
003
sub_3Name
NULL
.
.
.
.
sub_8Name
NULL

Solution:
select sn single_column
from
(
select subject_name sn,rn
from
(
select subject_codes,subject_name, rownum rn from tab_sub_code
)
union
select subject_codes sn,rn
from
(
select subject_codes,subject_name, rownum rn from tab_sub_code
)
union all
(
select NULL sn,rownum rn from tab_sub_code
)
order by rn,sn
)

No comments: