导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

纵向排列问题?

asdw001 2007-12-24 09:46:39


表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
*/
...全文
215 点赞 收藏 15
写回复
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
paula2008 2007-12-26
学习
回复
pl1069 2007-12-26
学习 学习 再学习
回复
shuihao2004 2007-12-26
回复
terrywangbuaa 2007-12-25
学习
回复
lsyyfj 2007-12-25
学习
回复
laowang2 2007-12-25
up
回复
asdw001 2007-12-24
谢谢
回复
中国风 2007-12-24
--在生成时,加多一个排序列row
以上第一个是动态调用系统表syscolumns
--
方法2静态方法
回复
中国风 2007-12-24

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

*/
回复
asdw001 2007-12-24
楼上的老大,我是新手啊,那么多代码,好晕啊!
回复
中国风 2007-12-24
列子:

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
回复
wzy_love_sly 2007-12-24
,0,1,9还以为是个串
回复
asdw001 2007-12-24
对liangCK
你的结果是从小到大插入列中,我要求顺序不能变插入到列中.
回复
liangCK 2007-12-24
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
回复
wzy_love_sly 2007-12-24
动态语句,帮顶
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告