SQL Server 行列互转!

--行列互转  

002 /******************************************************************************************************************************************************  

003 以学生成绩为例子，比较形象易懂  

004 日期:2008.06.06  

005 ******************************************************************************************************************************************************/  

006    

007 --1、行互列  

008 --> --> (Roy)生成測試數據  

009     

010 if not object_id('Class') is null 

011     drop table Class  

012 Go  

013 Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)  

014 Insert Class  

015 select N'张三',N'语文',78 union all 

016 select N'张三',N'数学',87 union all 

017 select N'张三',N'英语',82 union all 

018 select N'张三',N'物理',90 union all 

019 select N'李四',N'语文',65 union all 

020 select N'李四',N'数学',77 union all 

021 select N'李四',N'英语',65 union all 

022 select N'李四',N'物理',85   

023 Go  

024 --2000方法:  

025 动态:  

026    

027 declare @s nvarchar(4000)  

028 set @s='' 

029 Select     @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)' 

030 from Class group by[Course]  

031 exec('select [Student]'+@s+' from Class group by [Student]')  

032    

033    

034 生成静态:  

035    

036 select  

037     [Student],  

038     [数学]=max(case when [Course]='数学' then [Score] else 0 end),  

039     [物理]=max(case when [Course]='物理' then [Score] else 0 end),  

040     [英语]=max(case when [Course]='英语' then [Score] else 0 end),  

041     [语文]=max(case when [Course]='语文' then [Score] else 0 end)   

042 from  

043     Class   

044 group by [Student]  

045    

046 GO  

047 动态:  

048    

049 declare @s nvarchar(4000)  

050 Select     @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]  

051 exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')  

052    

053 生成静态:  

054 select *   

055 from  

056     Class   

057 pivot   

058     (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b  

059    

060 生成格式:  

061 /*  

062 Student 数学          物理          英语          语文  

063 ------- ----------- ----------- ----------- -----------  

064 李四      77          85          65          65  

065 张三      87          90          82          78  

066    

067 (2 行受影响)  

068 */  

069    

070 ------------------------------------------------------------------------------------------  

071 go  

072 --加上总成绩(学科平均分)  

073    

074 --2000方法:  

075 动态:  

076    

077 declare @s nvarchar(4000)  

078 set @s='' 

079 Select     @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)' 

080 from Class group by[Course]  

081 exec('select [Student]'+@s+',[总成绩]=sum([Score])  from Class group by [Student]')--加多一列(学科平均分用avg([Score]))  

082    

083 生成动态:  

084    

085 select  

086     [Student],  

087     [数学]=max(case when [Course]='数学' then [Score] else 0 end),  

088     [物理]=max(case when [Course]='物理' then [Score] else 0 end),  

089     [英语]=max(case when [Course]='英语' then [Score] else 0 end),  

090     [语文]=max(case when [Course]='语文' then [Score] else 0 end),  

091     [总成绩]=sum([Score]) --加多一列(学科平均分用avg([Score]))  

092 from  

093     Class   

094 group by [Student]  

095    

096 go  

097    

098 --2005方法:  

099    

100 动态:  

101    

102 declare @s nvarchar(4000)  

103 Select     @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号  

104 exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a   

105 pivot (max([Score]) for [Course] in('+@s+'))b ')  

106    

107 生成静态:  

108    

109 select  

110     [Student],[数学],[物理],[英语],[语文],[总成绩]   

111 from  

112     (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])  

113 pivot   

114     (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b   

115    

116 生成格式:  

117    

118 /*  

119 Student 数学          物理          英语          语文          总成绩  

120 ------- ----------- ----------- ----------- ----------- -----------  

121 李四      77          85          65          65          292  

122 张三      87          90          82          78          337  

123    

124 (2 行受影响)  

125 */  

126    

127 go  

128    

129 --2、列转行  

130 --> --> (Roy)生成測試數據  

131     

132 if not object_id('Class') is null 

133     drop table Class  

134 Go  

135 Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)  

136 Insert Class  

137 select N'李四',77,85,65,65 union all 

138 select N'张三',87,90,82,78  

139 Go  

140    

141 --2000:  

142    

143 动态:  

144    

145 declare @s nvarchar(4000)  

146 select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all  

147 +',[Score]='+quotename(Name)+' from Class' 

148 from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列  

149 order by Colid  

150 exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序  

151    

152 生成静态:  

153 select *   

154 from (select [Student],[Course]='数学',[Score]=[数学] from Class union all  

155 select [Student],[Course]='物理',[Score]=[物理] from Class union all  

156 select <s