有表 CAT 字段有 A B CD 四种类型 CAT DATE
A 20160101
B 20160202
C 20160403
D 2016 0501
要转换成CAT 20160101 20160202 20160403 20160501
A 1
B 1
C 1
D 1
A 20160101
B 20160202
C 20160403
D 2016 0501
要转换成CAT 20160101 20160202 20160403 20160501
A 1
B 1
C 1
D 1
case when DATE='20160101' then 1 end as "20160101",
case when DATE='20160102' then 1 end as "20160102",
case when DATE='20160103' then 1 end as "20160103",
case when DATE='20160104' then 1 end as "20160104"
from table;
这里date按照字符型处理的,如果是日期型自己处理加to_char 。
=========================================================================
select tab_test.cat
,max(case
when tab_test.dat = tab_test_rm.dat and rm = 4 then
1
else
null
end) dat1
,max(case
when tab_test.dat = tab_test_rm.dat and rm = 3 then
1
else
null
end) dat2
,max(case
when tab_test.dat = tab_test_rm.dat and rm = 2 then
1
else
null
end) dat3
,max(case
when tab_test.dat = tab_test_rm.dat and rm = 1 then
1
else
null
end) dat4
from tab_test
,(select dat, rm
from (select dat, dense_rank() over(order by dat desc) rm
from tab_test)
where rm <= 4
group by dat, rm) tab_test_rm
where tab_test_rm.dat = tab_test.dat
group by tab_test.cat
order by tab_test.cat
select years,
case
when a.years in (select years from test3 group by years) then (select mon from test3 where years=a.years and jd=1)
end 季度1,
case
when a.years in (select years from test3 group by years) then (select mon from test3 where years=a.years and jd=2)
end 季度2,
case
when a.years in (select years from test3 group by years) then (select mon from test3 where years=a.years and jd=3)
end 季度3
from test3 a group by years order by years;
这是我以前写的个行列倒置给你参考 希望能对你有帮助