各位大哥帮我一下,我想把如下的表形式转换成横表,改如何写语句
表(zl_serv)
www 200801 0
www 200802 1
www 200803 0
yyy 200801 1
yyy 200802 0
yyy 200803 1转换成
www 010
yyy 101
该如何写语句呢,先谢谢啦
表(zl_serv)
www 200801 0
www 200802 1
www 200803 0
yyy 200801 1
yyy 200802 0
yyy 200803 1转换成
www 010
yyy 101
该如何写语句呢,先谢谢啦
select
t.col_1 col_1,
lead(t.col_3,0)over(partition by t.col_1 col_1 order by t.col_1 col_1)||
lead(t.col_3,1)over(partition by t.col_1 col_1order by t.col_1 col_1)||
lead(t.col_3,2)over(partition by t.col_1 col_1 order by t.col_1 col_1) b,
row_number() over(partition by t.col_1 col_1 order by t.col_1 col_1) n
from
t)
where n=1没有测试过,应该行
select
t.col_1 col_1,
lead(t.col_3,0)over(partition by t.col_1 order by t.col_1 col_1)||
lead(t.col_3,1)over(partition by t.col_1 order by t.col_1 col_1)||
lead(t.col_3,2)over(partition by t.col_1 order by t.col_1 col_1) b,
row_number() over(partition by t.col_1 order by t.col_1 col_1) n
from
t)
where n=1
select temp.a,temp.b from
(
select tt.k a,replace(tt.h,';','') b,row_number() over(partition by tt.k order by tt.s desc) rn1 from
(
select g.status k,SYS_CONNECT_BY_PATH(g.id,';') h,g.rn s from
(select status,id,
lead(id,1) over(partition by status order by id) t,
row_number() over(partition by status order by id) rn
from rpt) g
start with rn=1
connect by g.id=prior g.t
) tt
) temp
where temp.rn1=1
SQL> select status,id from rpt; STATUS ID
---------- ----------
1 1
1 2
1 3
2 4
2 5
1 6
1 7
1 8已选择8行。SQL> select temp.a,temp.b from
2 (
3 select tt.k a,replace(tt.h,';','') b,row_number() over(partition by tt.k order by tt.s desc) rn1 from
4 (
5 select g.status k,SYS_CONNECT_BY_PATH(g.id,';') h,g.rn s from
6 (select status,id,
7 lead(id,1) over(partition by status order by id) t,
8 row_number() over(partition by status order by id) rn
9 from rpt) g
10 start with rn=1
11 connect by g.id=prior g.t
12 ) tt
13 ) temp
14 where temp.rn1=1; A B
---------- ----------
1 123678
2 45
LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS "NextHired"
FROM employees WHERE department_id = 30;LAST_NAME HIRE_DATE NextHired
------------------------- --------- ---------
Raphaely 07-DEC-94 18-MAY-95
Khoo 18-MAY-95 24-JUL-97
Tobias 24-JUL-97 24-DEC-97
Baida 24-DEC-97 15-NOV-98
Himuro 15-NOV-98 10-AUG-99
Colmenares 10-AUG-99