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

来源：互联网发布：网络言论自由辩论赛编辑：程序博客网时间：2024/06/06 00:09

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]

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