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
------解决方案--------------------
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
------解决方案--------------------
union all最基本的用法啊
------解决方案--------------------
行转列或列转行都比较死板，如果行列不固定很难做的。都是老问题了。
------解决方案--------------------
要么Union ALL要么你程序自己做
------解决方案--------------------
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