纵向排列问题?

[asdw001]

表tb的代码如下：
create table tb(id int,t text,a1 int,a2 int,a3 int) 
insert into tb values(1,'ta',0,1,9) 
insert into tb values(2,'tc',4,8,4) 
insert into tb values(3,'tt',7,3,3) 
insert into tb values(4,'rt',3,0,3) 
insert into tb values(5,'gg',7,3,6) 
insert into tb values(6,'sr',2,5,5) 
insert into tb values(7,'qq',4,5,9) 
insert into tb values(8,'sd',5,5,8) 
insert into tb values(9,'vs',5,0,4) 
insert into tb values(10,'ws',4,0,8) 
insert into tb values(11,'rf',9,3,4) 
go 

要求把每行a1,a2,a3的数据作纵向排到w列中，大小顺序不变。
结构如下：
id      t       w
1	ta	0
1	ta	1
1	ta	9
2	tc	4
2	tc	8
2	tc	4
3	tt	7
3	tt	3
3	tt	3
4	rt	3
4	rt	0
4	rt	3
5	gg	7
5	gg	3
5	gg	6
6	sr	2
6	sr	5
6	sr	5
7	qq	4
7	qq	5
7	qq	9
8	sd	5
8	sd	5
8	sd	8
9	vs	5
9	vs	0
9	vs	4
10	ws	4
10	ws	0
10	ws	8
11	rf	9
11	rf	3
11	rf	4

/*下面的代码有点问题，在处理第上面id为第6,8,10个数据时,顺序作了改变。
那怎样修改，才能得到上面的结果。
select * from 
( 
  select id , t , a1 w from tb 
  union all 
  select id , t , a2 w from tb 
  union all 
  select id , t , a3 w from tb 
) t 
order by id 
*/

[wzy_love_sly]

动态语句,帮顶

[liangCK]

select * from
(
select id , t , a1 w from tb
union all
select id , t , a2 w from tb
union all
select id , t , a3 w from tb
) t
order by id ,w

[asdw001]

对liangCK
你的结果是从小到大插入列中,我要求顺序不能变插入到列中.

[wzy_love_sly]

,0,1,9还以为是个串

[中国风]

列子:

create table A(id char(3),        num1 int,    num2 int,    num3  int,   num4 int)
insert A select '001',         80,         90,         50,         60 
insert A select '002',         84,         70,         60,         82

go


--SQL2005实现方法:
select 
        *
    from 
        A
    unpivot
        (num for col in([num1],[num2],[num3],[num4]))T2
        )tmp

--SQL2000实现:

---调用系统表动态生态
declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+' select ID,[num]='+quotename(Name,'''')+',Qty='+quotename(Name)+' from A'
from syscolumns 
where Name!=N'ID' and ID=object_id('A')--表名A，不包含列名为ID的其它列
order by colid asc
exec(@s+' order by ID asc,[num] asc')

--生成的静态语句
select ID,[num]='num1',Qty=[num1] from A union all  
select ID,[num]='num2',Qty=[num2] from A union all  
select ID,[num]='num3',Qty=[num3] from A union all  
select ID,[num]='num4',Qty=[num4] from A 
order by ID asc,[num] asc

/*

ID   num  Qty         
---- ---- ----------- 
001  num1 80
001  num2 90
001  num3 50
001  num4 60
002  num1 84
002  num2 70
002  num3 60
002  num4 82
------------------------------
*/


--动态方法:
declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+' select ID,[num]='+quotename(Name)+' from A'
from syscolumns 
where Name!=N'ID' and ID=object_id('A')
order by colid asc
exec(@s+' order by ID asc')

--生成的语句如下:

select ID,[num]=[num1] from A union all  
select ID,[num]=[num2] from A union all  
select ID,[num]=[num3] from A union all  
select ID,[num]=[num4] from A 
order by ID asc,[num] asc



/*
ID   num         
---- ----------- 
001  80
001  90
001  50
001  60
002  82
002  60
002  70
002  84
*/

go

declare @s2 nvarchar(4000)
set @s2=''
select @s2=@s2+','+quotename(id)+'=sum(case when ID='+quotename(ID,'''')+' then Qty else 0 end)'
from A
group by ID

declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+' select ID,[num]='+quotename(Name,'''')+',Qty='+quotename(Name)+' from A'
from syscolumns 
where Name!=N'ID' and ID=object_id('A')--表名A，不包含列名为ID的其它列
order by colid asc


---生成格式如下:

select 
	[num],
	[001]=sum(case when ID='001' then Qty else 0 end),
	[002]=sum(case when ID='002' then Qty else 0 end) 
from 
	( select ID,[num]='num1',Qty=[num1] from A union all  
	select ID,[num]='num2',Qty=[num2] from A union all  
	select ID,[num]='num3',Qty=[num3] from A union all  
	select ID,[num]='num4',Qty=[num4] from A) T 
group by [num] 

/*
num  001         002         
---- ----------- ----------- 
num1 80          84
num2 90          70
num3 50          60
num4 60          82

（所影响的行数为 4 行）


*/

---drop table A

[asdw001]

楼上的老大,我是新手啊,那么多代码,好晕啊!

[中国风]

create table tb(id int,t text,a1 int,a2 int,a3 int) 
insert into tb values(1,'ta',0,1,9) 
insert into tb values(2,'tc',4,8,4) 
insert into tb values(3,'tt',7,3,3) 
insert into tb values(4,'rt',3,0,3) 
insert into tb values(5,'gg',7,3,6) 
insert into tb values(6,'sr',2,5,5) 
insert into tb values(7,'qq',4,5,9) 
insert into tb values(8,'sd',5,5,8) 
insert into tb values(9,'vs',5,0,4) 
insert into tb values(10,'ws',4,0,8) 
insert into tb values(11,'rf',9,3,4) 

 go

declare @s nvarchar(1000)
select @s=isnull(@s+' union all ','')+'select id,t,[col]='+quotename(Name)+',row='+rtrim(colid)+' from Tb '
from syscolumns
where Name not in('id','t') and id=object_id('tb') order by colid 

exec(@s+' order by ID asc,row asc')

--
select 
	id,t,col
from 
	(select id,t,[col]=[a1],row=3 from Tb  
	union all 
	select id,t,[a2],row=4 from Tb  
	union all 
	select id,t,[a3],row=5 from Tb 
	)T
order by ID asc,row asc


/*
1	ta	0
1	ta	1
1	ta	9
2	tc	4
2	tc	8
2	tc	4
3	tt	7
3	tt	3
3	tt	3
4	rt	3
4	rt	0
4	rt	3
5	gg	7
5	gg	3
5	gg	6
6	sr	2
6	sr	5
6	sr	5
7	qq	4
7	qq	5
7	qq	9
8	sd	5
8	sd	5
8	sd	8
9	vs	5
9	vs	0
9	vs	4
10	ws	4
10	ws	0
10	ws	8
11	rf	9
11	rf	3
11	rf	4

*/

[中国风]

--在生成时，加多一个排序列row
以上第一个是动态调用系统表syscolumns
--
方法2静态方法

[asdw001]

谢谢

[laowang2]

up

[lsyyfj]

学习

[terrywangbuaa]

学习

[shuihao2004]

顶

[pl1069]

学习 学习 再学习

[paula2008]

学习