select id+id-1 as id,name from tB union all select id+id as id,score from tb order by id
呵呵偶是菜鸟,不知道这样能满足楼主的要求吗? create table TB (id int ,name nvarchar(10),score nvarchar(10)) insert into TB select 1,'张三','100' union select 2,'李四','80' union select 3,'王五','60'select id+id-1 as id,name from tB union all select id+id as id,score from tb order by id
列转行 --> --> (Roy)生成測試數據
if not object_id('Class') is null drop table Class Go Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int) Insert Class select N'李四',77,85,65,65 union all select N'张三',87,90,82,78 Go--2000:动态:declare @s nvarchar(4000) select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all +',[Score]='+quotename(Name)+' from Class' from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列 order by Colid exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序生成静态: select * from (select [Student],[Course]='数学',[Score]=[数学] from Class union all select [Student],[Course]='物理',[Score]=[物理] from Class union all select [Student],[Course]='英语',[Score]=[英语] from Class union all select [Student],[Course]='语文',[Score]=[语文] from Class)t order by [Student],[Course]go --2005:动态:declare @s nvarchar(4000) select @s=isnull(@s+',','')+quotename(Name) from syscolumns where ID=object_id('Class') and Name not in('Student') order by Colid exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')go select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in([数学],[物理],[英语],[语文]))b生成格式: /* Student Course Score ------- ------- ----------- 李四 数学 77 李四 物理 85 李四 英语 65 李四 语文 65 张三 数学 87 张三 物理 90 张三 英语 82 张三 语文 78(8 行受影响) */
select id, name into #t1 from table_1 order by idinsert into #t1 (id,name) select id,score from table_1select identity(int,1,1) as id,name,score into #t2 from #t1 order by id asc, name descselect * from #t2
--啊啊啊,上一个写错了 select id, name into #t1 from table_1 order by idinsert into #t1 (id,name) select id,score from table_1select identity(int,1,1) as id,name into #t2 from #t1 order by id asc, name descselect * from #t2
Create table score(id int identity(1,1) primary key,namec nvarchar(20),score decimal(6,2))insert into score(namec,score) select '张三',100 union all select '李四',80 union all select '王五',60 if object_id('tempdb..#t') is not null drop table #t create table #t(stuscore nvarchar(50),orderid int,id int,nameflag bit)--nameflag,是否是姓名的标识,1为姓名,0为成绩insert into #t(id,nameflag) select id,1 from score insert into #t(id,nameflag) select id,0 from scoreupdate a set a.stuscore = b.namec,orderid = 2 * b.id - 1 from #t a inner join score b on a.id = b.id where nameflag = 1 update a set a.stuscore = cast(b.score as nvarchar(10)),orderid = 2 * b.id from #t a inner join score b on a.id = b.id where nameflag = 0select stuscore from #t order by orderid
可以使用存储过程实现
select name
from TB
union all
select score as name
from TB) T
1 张三
2 李四
3 王五
4 100
5 80
6 60
create table TB (id int ,name nvarchar(10),score nvarchar(10))
insert into TB select 1,'张三','100' union
select 2,'李四','80' union
select 3,'王五','60'select id+id-1 as id,name from tB union all select id+id as id,score from tb order by id
--> --> (Roy)生成測試數據
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go--2000:动态:declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序生成静态:
select *
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all
select [Student],[Course]='物理',[Score]=[物理] from Class union all
select [Student],[Course]='英语',[Score]=[英语] from Class union all
select [Student],[Course]='语文',[Score]=[语文] from Class)t
order by [Student],[Course]go
--2005:动态:declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student')
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')go
select
Student,[Course],[Score]
from
Class
unpivot
([Score] for [Course] in([数学],[物理],[英语],[语文]))b生成格式:
/*
Student Course Score
------- ------- -----------
李四 数学 77
李四 物理 85
李四 英语 65
李四 语文 65
张三 数学 87
张三 物理 90
张三 英语 82
张三 语文 78(8 行受影响)
*/
select id, name into #t1
from table_1 order by idinsert into #t1 (id,name)
select id,score from table_1select identity(int,1,1) as id,name,score into #t2 from #t1 order by id asc, name descselect * from #t2
--啊啊啊,上一个写错了
select id, name into #t1
from table_1 order by idinsert into #t1 (id,name)
select id,score from table_1select identity(int,1,1) as id,name into #t2 from #t1 order by id asc, name descselect * from #t2
Create table score(id int identity(1,1) primary key,namec nvarchar(20),score decimal(6,2))insert into score(namec,score)
select '张三',100 union all
select '李四',80 union all
select '王五',60
if object_id('tempdb..#t') is not null
drop table #t
create table #t(stuscore nvarchar(50),orderid int,id int,nameflag bit)--nameflag,是否是姓名的标识,1为姓名,0为成绩insert into #t(id,nameflag) select id,1 from score
insert into #t(id,nameflag) select id,0 from scoreupdate a set a.stuscore = b.namec,orderid = 2 * b.id - 1 from #t a inner join score b on a.id = b.id where nameflag = 1
update a set a.stuscore = cast(b.score as nvarchar(10)),orderid = 2 * b.id from #t a inner join score b on a.id = b.id where nameflag = 0select stuscore from #t order by orderid