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
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;
