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
)
TIME_BUCKET group by time period
-
We all know how to sum up by year select
to_char(trunc(hiredate,'Y'),'YYYY') year, count(*) from scott.emp group by
trunc(hiredate,'Y') order by trunc(hire...
1 week ago
No comments:
Post a Comment