如何实现表的旋转,格式如下:
原表:
Field1 Field2
a1 b1
a2 b2
a3 b3
a4 b4
如休通过语句转换为另一个表如: newField1 newField2 newField3 newField4
a1 a2 a3 a4
b1 b2 b3 b4
当然原则字段有多列,记录数也不至四条
原表:
Field1 Field2
a1 b1
a2 b2
a3 b3
a4 b4
如休通过语句转换为另一个表如: newField1 newField2 newField3 newField4
a1 a2 a3 a4
b1 b2 b3 b4
当然原则字段有多列,记录数也不至四条
CREATE TABLE T(Field1 VARCHAR(20),Field2 VARCHAR(20))
INSERT INTO T SELECT 'a1','b1'
union all select 'a2','b2'
union all select 'a3','b3'
union all select 'a4','b4'--动态SQL
DECLARE @S1 VARCHAR(8000),@S2 varchar(8000),@I INT
SELECT @S1='',@S2='',@I=1
SELECT @S1=@S1+',newField'+ltrim(@I)+'=MAX(CASE WHEN Field2=''b'+ltrim(@i)+''' then Field1 END)',
@S2=@S2+',newField'+ltrim(@I)+'=MAX(CASE WHEN Field1=''a'+ltrim(@i)+''' then Field2 END)',
@I=@I+1
FROM T
SET @S1='SELECT '+STUFF(@S1,1,1,'') +' FROM T'
SET @S2='SELECT '+STUFF(@S2,1,1,'') +' FROM T'
EXEC(@S1+' UNION ALL '+@S2 )--结果
/*
newField1 newField2 newField3 newField4
-------------------- -------------------- -------------------- --------------------
a1 a2 a3 a4
b1 b2 b3 b4*/
--删除环境
DROP TABLE T
create table tb( O char(1),X varchar(10),Y varchar(10),Z varchar(10))
insert tb select 'A','x1','y1','z1'
union all select 'B','x2','y2','z2'
union all select 'C','x3','y3','z3'
go
declare @s1 varchar(8000),@s2 varchar(8000)
,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000)
,@i varchar(10)
select @s1='',@s2='',@s3='',@s4='',@s5='',@i='0'
select @s1=@s1+',@'+@i+' varchar(8000)'
,@s2=@s2+',@'+@i+'=''O='''''+name+''''''''
,@s3=@s3+'
,@'+@i+'=@'+@i+'+'',[''+cast([O] as varchar)+'']=''''''+cast(['+name+'] as varchar)+'''''''''
,@s4=@s4+',@'+@i+'=''select ''+@'+@i
,@s5=@s5+'+'' union all ''+@'+@i
,@i=cast(@i as int)+1
from syscolumns
where object_id('tb')=id and colid>1
order by colid
select @s1=stuff(@s1,1,1,'')
,@s2=stuff(@s2,1,1,'')
,@s3=stuff(@s3,1,5,'')
,@s4=stuff(@s4,1,1,'')
,@s5=stuff(@s5,1,15,'')
exec('declare '+@s1+'
select '+@s2+'
select '+@s3+'
from tb
select '+@s4+'
exec('+@s5+')')
/* print @s1: @0 varchar(8000),@1 varchar(8000),@2 varchar(8000)
print @s2: @0='O=''X''',@1='O=''Y''',@2='O=''Z'''
print @s3: @0=@0+',['+cast([O] as varchar)+']='''+cast([X] as varchar)+''''
,@1=@1+',['+cast([O] as varchar)+']='''+cast([Y] as varchar)+''''
,@2=@2+',['+cast([O] as varchar)+']='''+cast([Z] as varchar)+''''
print @s4: @0='select '+@0,@1='select '+@1,@2='select '+@2
print @s5: @0+' union all '+@1+' union all '+@2 */
--刪除測試環境
drop table tb
/*--顯示結果
O A B C
---- ---- ---- ----
X x1 x2 x3
Y y1 y2 y3
Z z1 z2 z3
(所影的行數只有3 行)
--*/