22,207
社区成员
发帖
与我相关
我的任务
分享
declare @sql varchar(8000)
set @sql = 'select name, batch_no= case name ';
select @sql = @sql +' when '''+name+''' then batch_no '
from test
set @sql =@sql + ' end ,raw_no=case name ';
select @sql = @sql +' when '''+name+''' then raw_no ' from test
set @sql = @sql +' end ,drawing_no= case name ';
select @sql = @sql +' when '''+name+''' then drawing_no ' from test
set @sql = @sql + 'end from test'
print @sql
exec (@sql)
create table test(
name varchar(20),
batch_no varchar(20),
raw_no varchar(20),
drawing_no varchar(20)
)
insert into test values('A','100','101','XX');
insert into test values('B','200','203','YY');
insert into test values('C','700','703','XX');
insert into test values('D','500','503','MM');
SELECT * FROM TEST
SELECT 名称='批次号',
MAX(CASE WHEN name='A' THEN batch_no END)AS 'A',
MAX(CASE WHEN name='B' THEN batch_no END)AS 'B',
MAX(CASE WHEN name='C' THEN batch_no END)AS 'C',
MAX(CASE WHEN name='D' THEN batch_no END)AS 'D'
FROM TEST
UNION ALL
SELECT 名称='炉批号',
MAX(CASE WHEN name='A' THEN raw_no END)AS 'A',
MAX(CASE WHEN name='B' THEN raw_no END)AS 'B',
MAX(CASE WHEN name='C' THEN raw_no END)AS 'C',
MAX(CASE WHEN name='D' THEN raw_no END)AS 'D'
FROM TEST
UNION ALL
SELECT 名称='图号',
MAX(CASE WHEN name='A' THEN drawing_no END)AS 'A',
MAX(CASE WHEN name='B' THEN drawing_no END)AS 'B',
MAX(CASE WHEN name='C' THEN drawing_no END)AS 'C',
MAX(CASE WHEN name='D' THEN drawing_no END)AS 'D'
FROM TEST
create table test(
name varchar(20) ,
batch_no varchar(20) ,
raw_no varchar(20) ,
drawing_no varchar(20)
)
insert into test values('A','100','101','XX');
insert into test values('B','200','203','YY');
insert into test values('C','700','703','XX');
insert into test values('D','500','503','MM');
declare @name nvarchar(4000),@batch_no nvarchar(4000),@raw_no nvarchar(4000),@drawing_no nvarchar(4000)
set @name=N''
set @batch_no=N''
set @raw_no=N''
set @drawing_no=N''
select @name=@name+N','''+name+N'''',
@batch_no=@batch_no+N','''+rtrim(batch_no)+N''' ',
@raw_no=@raw_no+N','''+rtrim(raw_no)+N'''',
@drawing_no=@drawing_no+N','''+rtrim(drawing_no)+N''''
from test
exec(N' select N''名称'''+@name+
N' union all '+
N' select N''批次号'''+@batch_no+
N' union all ' +
N' select N''炉批号'''+@raw_no+
N' union all ' +
N' select N''图号'''+@drawing_no)
drop table test
/*
名称 A B C D
批次号 100 200 700 500
炉批号 101 203 703 503
图号 XX YY XX MM
*/