34,576
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-08 17:03:27
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[ra] varchar(2),[rb] varchar(2),[rc] varchar(2),[rd] varchar(2))
insert [tb]
select 11,'aa','bb','cc','dd' union all
select 12,'ee','ff','gg','ll'
--------------开始查询--------------------------
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select id=row_number()over(order by id) , [aid] = id , [ar] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'id' and ID = object_id('tb') --表名tb
order by colid asc
exec(@sql + ' order by id ')
----------------结果----------------------------
/* id aid ar
-------------------- ----------- ----
1 11 aa
1 11 bb
1 11 cc
1 11 dd
2 12 ll
2 12 gg
2 12 ff
2 12 ee
(8 行受影响)
*/
declare @A table (id int,ra nvarchar(10),rb nvarchar(10),rc nvarchar(10),rd nvarchar(10))
insert into @A select 11,'aa','bb','cc','dd'
union all select 12,'ee','ff','gg','ll'
--try
select id=row_number()over(order by aid), * from
(
select id as aid,ra from @A
union all
select id,rb from @A
union all
select id,rc from @A
union all
select id,rd from @A) tb order by id
(2 行受影响)
id aid ra
-------------------- ----------- ----------
1 11 aa
2 11 bb
3 11 cc
4 11 dd
5 12 ll
6 12 gg
7 12 ff
8 12 ee
(8 行受影响)
/*
问题:如果上述两表互相换一下:即表结构和数据为:
姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94
想变成(得到如下结果):
姓名 课程 分数
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/
create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go
--SQL SERVER 2000 静态SQL。
select * from
(
select 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end
--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb,不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')
--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t
--SQL SERVER 2005 动态SQL,同SQL SERVER 2000 动态SQL。
--------------------
不是自增列的话, 如下:
select *,px=identity(int,1,1) into #t
from(
select id ,ra from a
union all
select id ,rb from a
union all
select id ,rc from a
union all
select id ,rd
)t
insert b select * from #t
insert into b select id,ra from a
insert b
select id ,ra from a
union all
select id ,rb from a
union all
select id ,rc from a
union all
select id ,rd
id 要是自增列