--静态的行转列

 --新建一个科目成绩表

 --三个字段：学生名称，科目，成绩

 CREATE TABLE SubjectScore

 (

     StuName nvarchar(20),

     SubjectName nvarchar(20),

     Fraction decimal(16,2)

 )

 --插入4条数据

 INSERT INTO SubjectScore

 VALUES(N'孔子',N'语文',99)

 INSERT INTO SubjectScore

 VALUES(N'孔子',N'数学',80)

 INSERT INTO SubjectScore

 VALUES(N'诸葛',N'语文',75.5)

 INSERT INTO SubjectScore

 VALUES(N'诸葛',N'数学',66)

 --行转列

 SELECT StuName,

 ISNULL(SUM(case SubjectName when N'语文' then isnull(Fraction,0)end),0)as N'语文',

 ISNULL(SUM(case SubjectName when N'数学' then isnull(Fraction,0)end),0)as N'数学'

 FROM SubjectScore

 GROUP BY StuName

 --如果加了个英语科目怎么办？没错，代码要改

 --下面我们加个英文科目

 INSERT INTO SubjectScore

 VALUES(N'诸葛',N'英语',66)

 --修改代码后行转列

 SELECT StuName,

 ISNULL(SUM(case SubjectName when N'语文' then isnull(Fraction,0)end),0)as N'语文',

 ISNULL(SUM(case SubjectName when N'数学' then isnull(Fraction,0)end),0)as N'数学',

 ISNULL(SUM(case SubjectName when N'英语' then isnull(Fraction,0)end),0)as N'英语'

 FROM SubjectScore

 GROUP BY StuName

 --静态列转行

 --表结构：我们建几个中文的字段：学生名称，语文，数学

 CREATE TABLE Demo_Stu

 (

     学生名称 nvarchar(20),

     语文 decimal(16,2),

     数学 decimal(16,2)

 )

 --一样插入4条数据

 INSERT INTO Demo_Stu

 VALUES(N'孔子',88,99)

 INSERT INTO Demo_Stu

 VALUES(N'孔子',99,69)

 INSERT INTO Demo_Stu

 VALUES(N'诸葛',88,55)

 INSERT INTO Demo_Stu

 VALUES(N'诸葛',77,63)

 --我们可以用union all 来实现,

 --不了解 union all怎么用的兄弟请自行百度

 select * from  (

     select 学生名称 as StuName,Subject=N'语文',Fraction=语文 from Demo_Stu

     union all

     select 学生名称 as StuName,Subject=N'数学',Fraction=数学 from Demo_Stu

 )p order by StuName

--动态的行转列

--科目成绩表

--三个字段：学生名称，科目，成绩

CREATE TABLE SubjectScore

(

    StuName nvarchar(20),

    SubjectName nvarchar(20),

    Fraction decimal(16,2)

)

--插入4条数据

INSERT INTO SubjectScore

VALUES(N'孔子',N'语文',99)

INSERT INTO SubjectScore

VALUES(N'孔子',N'数学',80)

INSERT INTO SubjectScore

VALUES(N'诸葛',N'语文',75.5)

INSERT INTO SubjectScore

VALUES(N'诸葛',N'数学',66)

/*

    动态行转列我们主要用到以下几个关键函数：

    2.PIVOT

    1.QUOTENAME

*/

DECLARE @sql_col NVARCHAR(4000)

DECLARE @sql_str  NVARCHAR(4000)

/*取表中行转列的所有科目*/

SELECT @sql_col = ISNULL(@sql_col+',','')+QUOTENAME(SubjectName) FROM SubjectScore GROUP BY SubjectName

SET @sql_str = '

SELECT * FROM SubjectScore p PIVOT(

    SUM([Fraction]) FOR [SubjectName] IN('+@sql_col+')

) pvt

order by StuName

'

EXEC (@sql_str)

--如果加了个英语科目怎么办？代码不需要改动

--下面我们加个历史科目，再执行上面的行转列代码

INSERT INTO SubjectScore

VALUES(N'诸葛',N'历史',88)

INSERT INTO SubjectScore

VALUES(N'孔子',N'历史',99)

 --动态态列转行

 --表结构：我们建几个中文的字段：学生名称，语文，数学

 CREATE TABLE Demo_Stu

 (

     学生名称 nvarchar(20),

     语文 decimal(16,2),

     数学 decimal(16,2)

 )

 --一样插入4条数据

 INSERT INTO Demo_Stu

 VALUES(N'孔子',88,99)

 INSERT INTO Demo_Stu

 VALUES(N'孔子',99,69)

 INSERT INTO Demo_Stu

 VALUES(N'诸葛',88,55)

 INSERT INTO Demo_Stu

 VALUES(N'诸葛',77,63)

 --列传行

   --列转行的方案来源于：Joe.TJ

   --列转行的动态方案：UNPIVOT,sql2005及以后版本

   --因为行是动态所以这里就从INFORMATION_SCHEMA.COLUMNS视图中获取列来构造行，同样也使用了XML处理。

   DECLARE @SQL NVARCHAR(4000)=N'';

  SET @SQL=STUFF((SELECT N','+QUOTENAME(COLUMN_NAME ) FROM INFORMATION_SCHEMA.COLUMNS

  WHERE ORDINAL_POSITION>1 AND TABLE_NAME='Demo_Stu'

  FOR XML PATH('')),1,1,N'')

  SET @SQL=N'SELECT *

           from dbo.Demo_Stu

           unpivot(分数 for 科目 in('+@SQL+'))as up';

  EXEC SP_EXECUTESQL @SQL;