行列互换

来源：互联网发布：万网已备案域名购买编辑：程序博客网时间：2024/04/30 06:01

--> --> (Roy)生成測試數據 if not object_id('Class') is null    drop table ClassGoCreate table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)Insert Classselect N'李四',77,85,65,65 union allselect N'张三',87,90,82,78godeclare @s nvarchar(4000),@s2 nvarchar(4000),@s3 nvarchar(4000),@s4 nvarchar(4000)select @s=isnull(@s+',','declare ')+'@'+rtrim(Colid)+' nvarchar(4000)',@s2=isnull(@s2+',','select ')+'@'+rtrim(Colid)+'='''+case when @s2 is not null then 'union all select' else ' select ' end+'  [科目]='''+quotename(Name,'''')+'''''',@s3=isnull(@s3,'')+'select @'+rtrim(Colid)+'=@'+rtrim(Colid)+'+'',''+quotename([Student])+''=''+quotename('+quotename(Name)+','''''''')  from Class ',@s4=isnull(@s4+'+','')+'@'+rtrim(Colid)from syscolumns whereid=object_id('Class') and Name not in('Student')--print @s+' '+@s2+' '+@s3+' exec('+@s4+')' 显示执行语句exec(@s+' '+@s2+' '+@s3+' exec('+@s4+')')/*科目   李四   张三---- ---- ----数学   77   87物理   85   90英语   65   82语文   65   78*/

另类显示格式，把列数转为行数显示

if not object_id('Class') is null    drop table ClassGoCreate table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)Insert Classselect N'张三',N'语文',78 union allselect N'张三',N'数学',87 union allselect N'张三',N'英语',82 union allselect N'张三',N'物理',90 union allselect N'李四',N'语文',65 union allselect N'李四',N'数学',77 union allselect N'李四',N'英语',65 union allselect N'李四',N'物理',85 GODECLARE @Sql NVARCHAR(max)SET @Sql=(SELECT ',[Col'+RTRIM(ROW_NUMBER()OVER(ORDER BY RAND()))+']='''+[Student]+'''' FROM dbo.Class FOR XML  PATH(''))SET @Sql=STUFF(@Sql,1,1,'SELECT ')+STUFF((SELECT ',[Col1]='''+[Course]+'''' FROM dbo.Class FOR XML  PATH('')),1,1,' UNION ALL SELECT ')SET @Sql=@Sql+STUFF((SELECT ',[Col1]='''+RTRIM([Score])+'''' FROM dbo.Class FOR XML  PATH('')),1,1,' UNION ALL SELECT ')EXEC(@Sql)/*Col1Col2Col3Col4Col5Col6Col7Col8张三张三张三张三李四李四李四李四语文数学英语物理语文数学英语物理7887829065776585*/

其它方法

http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html?seed=562318242