比如说人的名字在数据库中是在一列存放的!要显示成 A B C D E F G H J K 而在数据库中是: A B C D E F G H K
declare @t table(id int identity,name varchar(10)) insert into @t(name) select 'A' union all select 'B' union all select 'C' union all select 'D' union all select 'E' union all select 'F' union all select 'G' union all select 'H' union all select 'K'select * from @tselect a.name ,b.name,c.name,d.name,e.name from @t a left join @t b on a.id %5=1 and b.id %5=2 and b.id=a.id+1 left join @t c on c.id %5=3 and c.id=b.id+1 left join @t d on d.id%5=4 and d.id=c.id+1 left join @t e on e.id %5=0 and e.id=d.id+1 where a.id %5=1
谢谢!还有其它的办法吗?用一条sql语句就能直接出来的?
2005实现方法: declare @ta table(A varchar(2),B varchar(2), C varchar(2),D varchar(2),E varchar(2)) insert @ta select 'F','G','H', 'J' ,'K' insert @ta select 'F','G','H', 'J' ,'K'select orders from @ta unpivot (orders for 原因 in(a,b,c,d,e))as tb(1 行受影响)(1 行受影响) orders ------ F G H J K F G H J K(10 行受影响)
A B C D E
F G H J K
而在数据库中是:
A
B
C
D
E
F
G
H
K
select 'A' union all select
'B'
union all select
'C'
union all select
'D'
union all select
'E'
union all select
'F'
union all select
'G'
union all select
'H'
union all select
'K'select * from @tselect a.name ,b.name,c.name,d.name,e.name from @t a
left join @t b on a.id %5=1 and b.id %5=2 and b.id=a.id+1
left join @t c on c.id %5=3 and c.id=b.id+1
left join @t d on d.id%5=4 and d.id=c.id+1
left join @t e on e.id %5=0 and e.id=d.id+1
where a.id %5=1
declare @ta table(A varchar(2),B varchar(2), C varchar(2),D varchar(2),E varchar(2))
insert @ta select 'F','G','H', 'J' ,'K'
insert @ta select 'F','G','H', 'J' ,'K'select orders
from @ta
unpivot
(orders for 原因 in(a,b,c,d,e))as tb(1 行受影响)(1 行受影响)
orders
------
F
G
H
J
K
F
G
H
J
K(10 行受影响)