数据库表将一个表的列PIVOT，UNPIVOT 应用小示例

CREATE TABLE [dbo].[Table_per1](
 [id] [int] NOT NULL,
 [name] [nvarchar](1000) NOT NULL,
 [sex] [nchar](10) NULL,
 [age] [int] NULL,
 [CH] [float] NULL,
 [Math] [float] NULL,
 [Brith] [datetime] NULL
) ON [PRIMARY]

GO

insert     Table_per1     (id,name,sex,age,CH,Math,Brith)          values     (     1,'a','a         ',23,88,90,'2010-01-10 00:00:00.000')
insert     Table_per1     (id,name,sex,age,CH,Math,Brith)          values     (     2,'b','c         ',34,89,91,'2010-01-10 00:00:00.000')
insert     Table_per1     (id,name,sex,age,CH,Math,Brith)          values     (     3,'c','b         ',16,90,92,'2010-01-10 00:00:00.000')

 

SELECT  *
FROM    Table_per1
go
SELECT  *
FROM    ( SELECT    id ,
                    name ,
                    sex ,
                    age ,
                    CH ,
                    Math ,
                    Brith
          FROM      dbo.Table_per1
        ) a PIVOT( AVG(age) FOR ch IN ( [88], [89], [90] ) ) AS b
       
go    

SELECT  id ,
        name ,
        SUBJECTs ,
        score
FROM    ( SELECT    id ,
                    name ,
                    sex ,
                    age ,
                    CH ,
                    Math ,
                    Brith
          FROM      dbo.Table_per1
        ) a UNPIVOT( SUBJECTs FOR score IN ( [CH], [Math] ) ) b
 go