一行记录多列显示的问题

Question

select col1,col6
from tab
union all
select col2,col7
from tab
union all
select col3,col8
from tab
union all
select col4,col9
from tab
union all
select col5,col10
from tab

Answer 1

create table #
(f1 varchar(100))
insert into #
select '1,2,3,4,5,a,b,c,d,e'
select top 100 id=identity(int,1,1) into #1 from sysobjectsselect 
substring(a.f1,b.id,charindex(',',a.f1+',',b.id)-b.id)as name
into #2
from # a ,#1 b
where substring(','+a.f1,b.id,1)=','
select a.name,b.name
from
(select *,col=(select count(1) from #2 where name<a.name and isnumeric(name)=1) from #2 a where isnumeric(name)=1 )a,
(select *,col=(select count(1) from #2 where name<a.name and isnumeric(name)=0) from #2 a where isnumeric(name)=0 )b
where a.col=b.col/*
name                                                                                                 name                                                                                                 
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- 
1                                                                                                    a
2                                                                                                    b
3                                                                                                    c
4                                                                                                    d
5                                                                                                    e（所影响的行数为 5 行）
*/