前些天提的问题,已网友帮助得到了解决,可是还有些疑问
id name Number A1 A2 A3......A40 (一条记录)
1 jick 40 05 01 02 11 (记录中的值)我想把这一条记录改写成下面这个样子的一个表id name Number AA
1 jick 40 05
2 jick 40 01
3 jick 40 02
.
.
.
.
40 jick 40 11-----------------------------------------------------------
上一回答案:
drop table tbtest
go
create table tbtest(id int,name varchar(10),Number int,A1 varchar(10),A2 varchar(10),A3 varchar(10),A40 varchar(10))
insert into tbtest
select 1,'jack',40,'05','01','02','11'
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+'union all'+char(13)+'select name,Number,'+name+' as [AA] from tbtest'+char(13)
from syscolumns
where id=object_id('tbtest') and name not in ('id','name','Number')
select @sql='select id=identity(int,1,1),* into #t from('+stuff(@sql,1,len('union all')+1,'')+')t'+char(13)+'select * from #t'
exec(@sql)
/*
(所影响的行数为 4 行)
id name Number AA
----------- ---------- ----------- ----------
1 jack 40 05
2 jack 40 01
3 jack 40 02
4 jack 40 11
*/ 可是这个数据多了之后,不能案照A1 A2 的顺序得到结果 如下:请帮帮忙!
create table tbtest(id int,name varchar(10),Number int,A1 varchar(10),A2 varchar(10),A3 varchar(10),A4 varchar(10),A5 varchar(10),A6 varchar(10),A7 varchar(10),A8 varchar(10),A9 varchar(10),A10 varchar(10),A11 varchar(10),A12 varchar(10))
insert into tbtest
select 1,'jack',40,'05','01','02','11','21','41','92','77','88','54','21','45'declare @sql varchar(8000)
set @sql=''
select @sql=@sql+'union all'+char(13)+'select name,Number,'+name+' as [AA] from tbtest'+char(13)
from syscolumns
where id=object_id('tbtest') and name not in ('id','name','Number')
select @sql='select id=identity(int,1,1),* into #t from('+stuff(@sql,1,len('union all')+1,'')+')t'+char(13)+'select * from #t'
exec(@sql)
-------------------
1 jack 40 05
2 jack 40 54
3 jack 40 21
4 jack 40 45
5 jack 40 01
6 jack 40 02
7 jack 40 11
8 jack 40 21
9 jack 40 41
10 jack 40 92
11 jack 40 77
12 jack 40 88
注意看后右面一例的顺序。
id name Number A1 A2 A3......A40 (一条记录)
1 jick 40 05 01 02 11 (记录中的值)我想把这一条记录改写成下面这个样子的一个表id name Number AA
1 jick 40 05
2 jick 40 01
3 jick 40 02
.
.
.
.
40 jick 40 11-----------------------------------------------------------
上一回答案:
drop table tbtest
go
create table tbtest(id int,name varchar(10),Number int,A1 varchar(10),A2 varchar(10),A3 varchar(10),A40 varchar(10))
insert into tbtest
select 1,'jack',40,'05','01','02','11'
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+'union all'+char(13)+'select name,Number,'+name+' as [AA] from tbtest'+char(13)
from syscolumns
where id=object_id('tbtest') and name not in ('id','name','Number')
select @sql='select id=identity(int,1,1),* into #t from('+stuff(@sql,1,len('union all')+1,'')+')t'+char(13)+'select * from #t'
exec(@sql)
/*
(所影响的行数为 4 行)
id name Number AA
----------- ---------- ----------- ----------
1 jack 40 05
2 jack 40 01
3 jack 40 02
4 jack 40 11
*/ 可是这个数据多了之后,不能案照A1 A2 的顺序得到结果 如下:请帮帮忙!
create table tbtest(id int,name varchar(10),Number int,A1 varchar(10),A2 varchar(10),A3 varchar(10),A4 varchar(10),A5 varchar(10),A6 varchar(10),A7 varchar(10),A8 varchar(10),A9 varchar(10),A10 varchar(10),A11 varchar(10),A12 varchar(10))
insert into tbtest
select 1,'jack',40,'05','01','02','11','21','41','92','77','88','54','21','45'declare @sql varchar(8000)
set @sql=''
select @sql=@sql+'union all'+char(13)+'select name,Number,'+name+' as [AA] from tbtest'+char(13)
from syscolumns
where id=object_id('tbtest') and name not in ('id','name','Number')
select @sql='select id=identity(int,1,1),* into #t from('+stuff(@sql,1,len('union all')+1,'')+')t'+char(13)+'select * from #t'
exec(@sql)
-------------------
1 jack 40 05
2 jack 40 54
3 jack 40 21
4 jack 40 45
5 jack 40 01
6 jack 40 02
7 jack 40 11
8 jack 40 21
9 jack 40 41
10 jack 40 92
11 jack 40 77
12 jack 40 88
注意看后右面一例的顺序。
tbtest(
id int,
name varchar(10),
Number int,
A1 varchar(10),
A2 varchar(10),
A3 varchar(10),
A4 varchar(10),
A5 varchar(10),
A6 varchar(10),
A7 varchar(10),
A8 varchar(10),
A9 varchar(10),
A10 varchar(10),
A11 varchar(10),
A12 varchar(10)
)
insert into tbtest
select 1,'jack',40,'05','01','02','11','21','41','92','77','88','54','21','45'declare @sql varchar(8000)
set @sql=''
select @sql=@sql+'union all'+char(13)+'select name,Number,'+name+' as [AA] from tbtest'+char(13)
from syscolumns
where id=object_id('tbtest') and name not in ('id','name','Number') order by colorder--此处加order by colorder
select @sql='select id=identity(int,1,1),* into #t from('+stuff(@sql,1,len('union all')+1,'')+')t'+char(13)+'select * from #t'
exec(@sql)
加order by colorder
试试