17,382
社区成员
发帖
与我相关
我的任务
分享求行转列 。。。。 奖励多多（语句精简）
create table celltable
(
pname varchar(10),
p1 varchar(10),
p2 varchar(10),
p3 varchar(10),
p4 varchar(10),
p5 varchar(10),
p6 varchar(10),
p7 varchar(10),
p8 varchar(10),
p9 varchar(10),
)
insert into celltable values ('1001','A1','A2','A3','','','A6','A7','A8','A9')
insert into celltable values ('1002','','','A1','A2','A3','A4','A5','A6','A7')
insert into celltable values ('1003','A1','A5','A2','A4','A3','A6','A7','A8','A9')
--转化结果
PName P (根据P1-P9顺序依次下来 空值去掉)
1001 A1
1001 A2
1001 A3
1001 A6
1001 A7
1001 A8
1002 A1
1002 ..
1002 A7
1003 A1
1003 A5
1003 A3
1003 ..
1003 A9
...全文
请发表友善的回复…
发表回复
Mirror然 2014-08-07
- 打赏
- 举报
great,thanks for share[/quote]
这个函数在sql2008已经有了 可惜不是11G 看到oracle的语法 有时候还是觉得sql 好使
CT_LXL 2014-08-07
- 打赏
- 举报
great,thanks for share
bw555 2014-08-07
- 打赏
- 举报
pivot /unpivot
很强大的东西，呵呵，刚去学习了下
http://www.cnblogs.com/zlja/archive/2011/12/10/2449515.html
sych888 2014-08-07
- 打赏
- 举报
11g pivot /unpivot
CT_LXL 2014-08-06
- 打赏
- 举报
你前面都是按照a1,a2...a8,这三个又按照a1,a5,a3,请问你排序根据是什么？
[/quote]
麻烦版主了 [/quote]
[/quote]
select *
from (select pname, p1 P, 1 seq
from celltable
union all
select pname, p2 P, 2 seq
from celltable
union all
select pname, p3 P, 3 seq
from celltable
union all
select pname, p4 P, 4 seq
from celltable
union all
select pname, p5 P, 5 seq
from celltable
union all
select pname, p6 P, 6 seq
from celltable
union all
select pname, p7 P, 7 seq
from celltable
union all
select pname, p8 P, 8 seq
from celltable
union all
select pname, p9 P, 9 seq
from celltable)
where p is not null
order by pname, seq;Mirror然 2014-08-06
- 打赏
- 举报
你前面都是按照a1,a2...a8,这三个又按照a1,a5,a3,请问你排序根据是什么？
[/quote]
麻烦版主了
Mirror然 2014-08-06
- 打赏
- 举报
你前面都是按照a1,a2...a8,这三个又按照a1,a5,a3,请问你排序根据是什么？
[/quote]
根据p1 - p9 顺序来
p1 值是 a1
p2 值是 a9
p3 值是 a3
显示的话就是 a1,a9,a3
CT_LXL 2014-08-06
- 打赏
- 举报
你前面都是按照a1,a2...a8,这三个又按照a1,a5,a3,请问你排序根据是什么？
Mirror然 2014-08-06
- 打赏
- 举报
select * from (
select pname,p1 P from celltable union all
select pname,p2 P from celltable union all
select pname,p3 P from celltable union all
select pname,p4 P from celltable union all
select pname,p5 P from celltable union all
select pname,p6 P from celltable union all
select pname,p7 P from celltable union all
select pname,p8 P from celltable union all
select pname,p9 P from celltable )
where p is not null
order by pname,pCT_LXL 2014-08-06
- 打赏
- 举报
select * from (
select pname,p1 P from celltable union all
select pname,p2 P from celltable union all
select pname,p3 P from celltable union all
select pname,p4 P from celltable union all
select pname,p5 P from celltable union all
select pname,p6 P from celltable union all
select pname,p7 P from celltable union all
select pname,p8 P from celltable union all
select pname,p9 P from celltable )
where p is not null
order by pname,pMirror然 2014-08-06
- 打赏
- 举报
select * from (
select pname,p1 P from celltable union all
select pname,p2 P from celltable union all
select pname,p3 P from celltable union all
select pname,p4 P from celltable union all
select pname,p5 P from celltable union all
select pname,p6 P from celltable union all
select pname,p7 P from celltable union all
select pname,p8 P from celltable union all
select pname,p9 P from celltable )
where p is not null
order by pname,p
一颗向往数据的心丶 2014-08-05
- 打赏
- 举报
要么Union ALL要么你程序自己做
CT_LXL 2014-08-05
- 打赏
- 举报
有没其他的方法 新手赐教[/quote]
行转列或列转行都比较死板，如果行列不固定很难做的。都是老问题了。
[/quote]
行转列或列转行都比较死板，如果行列不固定很难做的。都是老问题了。Mirror然 2014-08-05
- 打赏
- 举报
有没其他的方法 新手赐教
Mirror然 2014-08-05
- 打赏
- 举报
select * from (
select pname,p1 P from celltable union all
select pname,p2 P from celltable union all
select pname,p3 P from celltable union all
select pname,p4 P from celltable union all
select pname,p5 P from celltable union all
select pname,p6 P from celltable union all
select pname,p7 P from celltable union all
select pname,p8 P from celltable union all
select pname,p9 P from celltable )
where p is not null
order by pname,pbw555 2014-08-05
- 打赏
- 举报
union all最基本的用法啊
CT_LXL 2014-08-05
- 打赏
- 举报
select * from (
select pname,p1 P from celltable union all
select pname,p2 P from celltable union all
select pname,p3 P from celltable union all
select pname,p4 P from celltable union all
select pname,p5 P from celltable union all
select pname,p6 P from celltable union all
select pname,p7 P from celltable union all
select pname,p8 P from celltable union all
select pname,p9 P from celltable )
where p is not null
order by pname,p
