SQL Server中的行列转换问题
?
SQL Server中的行列转换问题?
普通行列转换仅针对sql?server?2000提供静态和动态写法。?增加sql?server?2005的有关写法。?PIVOT用于将列值旋转为列名（即行转列），在SQL Server?2000可以用聚合函数配合CASE语句实现
PIVOT的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P
完整语法：
table_source
PIVOT(
聚合函数（value_column）
FOR pivot_column
IN(<column_list>)
)
?
UNPIVOT用于将列明转为列值（即列转行），在SQL Server 2000可以用UNION来实现
完整语法：
table_source
UNPIVOT(
value_column
FOR pivot_column
IN(<column_list>)
)
?
注意：PIVOT、UNPIVOT是SQL Server 2005?的语法，使用需修改数据库兼容级别
?在数据库属性->选项->兼容级别改为 ? 90
问题：假设有张学生成绩表(tb)如下:姓名?课程?分数张三?语文?74张三?数学?83张三?物理?93李四?语文?74李四?数学?84李四?物理?94想变成(得到如下结果)：?姓名?语文?数学?物理?----?----?----?----李四?74???84???94张三?74???83???93-------------------*/?create?table?tb(姓名?varchar(10)?,?课程?varchar(10)?,?分数?int)insert?into?tb?values('张三'?,?'语文'?,?74)insert?into?tb?values('张三'?,?'数学'?,?83)insert?into?tb?values('张三'?,?'物理'?,?93)insert?into?tb?values('李四'?,?'语文'?,?74)insert?into?tb?values('李四'?,?'数学'?,?84)insert?into?tb?values('李四'?,?'物理'?,?94)go?--SQL?SERVER?2000?静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)select?姓名?as?姓名?,??max(case?课程?when?'语文'?then?分数?else?0?end)?语文,??max(case?课程?when?'数学'?then?分数?else?0?end)?数学,??max(case?课程?when?'物理'?then?分数?else?0?end)?物理from?tbgroup?by?姓名?--SQL?SERVER?2000?动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)--变量按sql语言顺序赋值declare?@sql?varchar(8000)set?@sql?=?'select?姓名?'select?@sql?=?@sql?+?'?,?max(case?课程?when?'''?+?课程?+?'''?then?分数?else?0?end)?['?+?课程?+?']'from?(select?distinct?课程?from?tb)?as?a --同from tb group by课程，默认按课程名排序set?@sql?=?@sql?+?'?from?tb?group?by?姓名'exec(@sql)???--使用isnull(),变量先确定动态部分
declare@sqlvarchar(8000)
select@sql=isnull(@sql+',','')+' max(case课程when '''+课程+''' then分数else 0 end)
['+课程+']'
from(selectdistinct课程fromtb)asa??????
set@sql='select姓名,'+@sql+' from tb group by姓名'
exec(@sql)
??--SQL?SERVER?2005?静态SQL。select?*?from?tb?pivot?(max(分数)?for?课程?in?(语文,数学,物理))?a?--SQL?SERVER?2005?动态SQL。--使用isnull()declare?@sql?varchar(8000)select?@sql?=?isnull(@sql?+?','?,?'')?+?课程?from?tb?group?by?课程exec?('select?*?from?tb?pivot?(max(分数)?for?课程?in?('?+?@sql?+?'))?a')??--使用stuff()
declare@sqlvarchar(8000)
set@sql=''??--初始化变量@sql
select@sql=@sql+','+课程fromtbgroupby课程--变量多值赋值
set@sql=stuff(@sql,1,1,'')--去掉首个','
set@sql='select * from tb?pivot (max(分数) for课程in ('+@sql+'))a'
exec(@sql)
?-----------------------------------?/*问题：在上述结果的基础上加平均分，总分，得到如下结果：姓名?语文?数学?物理?平均分?总分?----?----?----?----?------?----李四?74???84???94???84.00??252张三?74???83???93???83.33??250*/?--SQL?SERVER?2000?静态SQL。select?姓名?姓名,??max(case?课程?when?'语文'?then?分数?else?0?end)?语文,??max(case?课程?when?'数学'?then?分数?else?0?end)?数学,??max(case?课程?when?'物理'?then?分数?else?0?end)?物理,??cast(avg(分数*1.0)?as?decimal(18,2))?平均分,??sum(分数)?总分from?tbgroup?by?姓名?--SQL?SERVER?2000?动态SQL。declare?@sql?varchar(8000)set?@sql?=?'select?姓名?'select?@sql?=?@sql?+?',?max(case?课程?when?'''?+?课程?+?'''?then?分数?else?0?end)?['?+?课程?+?']'from?(select?distinct?课程?from?tb)?as?aset?@sql?=?@sql?+?'?,?cast(avg(分数*1.0)?as?decimal(18,2))?平均分?,?sum(分数)?总分?from?tb?group?by?姓名'exec(@sql)??--SQL?SERVER?2005?静态SQL。select?m.*?,?n.平均分?,?n.总分?from(select?*?from?(select?*?from?tb)?a?pivot?(max(分数)?for?课程?in?(语文,数学,物理))?b)?m,(select?姓名?,?cast(avg(分数*1.0)?as?decimal(18,2))?平均分,?sum(分数)?总分?from?tb?group?by?姓名)?nwhere?m.姓名?=?n.姓名?--SQL?SERVER?2005?动态SQL。--使用isnull()declare?@sql?varchar(8000)select?@sql?=?isnull(@sql?+?','?,?'')?+?课程?from?tb?group?by?课程exec?('select?m.*?,?n.平均分?,?n.总分?from(select?*?from?(select?*?from?tb)?a?pivot?(max(分数)?for?课程?in?('?+?@sql?+?'))?b)?m?,?(select?姓名?,?cast(avg(分数*1.0)?as?decimal(18,2))?平均分,?sum(分数)?总分?from?tb?group?by?姓名)?nwhere?m.姓名?=?n.姓名')?drop?table?tb???????
--使用stuff()
--
declare@sqlvarchar(8000)
set@sql=''??--初始化变量@sql
select@sql=@sql+','+课程fromtbgroupby课程--变量多值赋值
--同select @sql = @sql + ','+课程from (select distinct课程from tb)a
set@sql=stuff(@sql,1,1,'')--去掉首个','
set@sql='select m.* , n.总分,n.平均分from
(select * from (select * from tb) a pivot (max(分数) for课程in ('+@sql+')) b) m ,
(select姓名,sum(分数)总分, cast(avg(分数*1.0) as decimal(18,2))平均分from tb group
by姓名) n
where m.姓名= n.姓名'
exec(@sql)
?------------------------------------------------------------?/*问题：如果上述两表互相换一下：即表结构和数据为：姓名?语文?数学?物理张三?74 83 93李四?74 84 94想变成(得到如下结果)：?姓名?课程?分数?----?----?----李四?语文?74李四?数学?84李四?物理?94张三?语文?74张三?数学?83张三?物理?93--------------*/?create?table?tb(姓名?varchar(10)?,?语文?int?,?数学?int?,?物理?int)insert?into?tb?values('张三',74,83,93)insert?into?tb?values('李四',74,84,94)go?--SQL?SERVER?2000?静态SQL。select?*?from(?select?姓名?,?课程?=?'语文'?,?分数?=?语文?from?tb??union?all?select?姓名?,?课程?=?'数学'?,?分数?=?数学?from?tb?union?all?select?姓名?,?课程?=?'物理'?,?分数?=?物理?from?tb)?torder?by?姓名?,?case?课程?when?'语文'?then?1?when?'数学'?then?2?when?'物理'?then?3?end?--SQL?SERVER?2000?动态SQL。--调用系统表动态生态。declare?@sql?varchar(8000)select?@sql?=?isnull(@sql?+?'?union?all?'?,?''?)?+?'?select?姓名?,?[课程]?=?'?+?quotename(Name?,?'''')?+?'?,?[分数]?=?'?+?quotename(Name)?+?'?from?tb'from?syscolumns?where?name!?=?N'姓名'?and?ID?=?object_id('tb')?--表名tb，不包含列名为姓名的其它列order?by?colid?ascexec(@sql?+?'?order?by?姓名?')?--SQL?SERVER?2005?动态SQL。select?姓名,?课程,?分数?from?tb?unpivot?(分数?for?课程?in([语文],?[数学],?[物理]))?t?--SQL?SERVER?2005?动态SQL，同SQL?SERVER?2000?动态SQL。?--------------------/*问题：在上述的结果上加个平均分，总分，得到如下结果：姓名?课程???分数----?------?------李四?语文???74.00李四?数学???84.00李四?物理???94.00李四?平均分?84.00李四?总分???252.00张三?语文???74.00张三?数学???83.00张三?物理???93.00张三?平均分?83.33张三?总分???250.00------------------*/?select?*?from(?select?姓名?as?姓名?,?课程?=?'语文'?,?分数?=?语文?from?tb??union?all?select?姓名?as?姓名?,?课程?=?'数学'?,?分数?=?数学?from?tb?union?all?select?姓名?as?姓名?,?课程?=?'物理'?,?分数?=?物理?from?tb?union?all?select?姓名?as?姓名?,?课程?=?'平均分'?,?分数?=?cast((语文?+?数学?+?物理)*1.0/3?as?decimal(18,2))?from?tb?union?all?select?姓名?as?姓名?,?课程?=?'总分'?,?分数?=?语文?+?数学?+?物理?from?tb)?torder?by?姓名?,?case?课程?when?'语文'?then?1?when?'数学'?then?2?when?'物理'?then?3?when?'平均分'?then?4?when?'总分'?then?5?end?drop?table?tb?????