August 26 2014

sqlserver UNPIVOT/pivot

declare @t table(usser int ,no int ,a int,b int, c int)
insert into @t select 1,1,21,34,24
union all select 1,2,42,25,16
SELECT usser,no,Type=attribute, Num=value
FROM @t
UNPIVOT
(
value FOR attribute IN([a], [b], [c])
) AS UPV
—————————————————

CREATE TABLE [dbo].[tbl_Student](
[ID] [int] IDENTITY(1,1) NOT NULL,
[学生ID] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[学生姓名] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[课程ID] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[课程名称] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[成绩] [int] NULL,
[教师ID] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[教师姓名] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]

select * from tbl_Student
————————————————————————–

UNPIVOT/pivot
可以使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。
PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合。
UNPIVOT 与 PIVOT 执行相反的操作,将表值表达式的列转换为列值。
语法如下:
SELECT <非透视的列>,
[第一个透视的列] AS <列名称>,
[第二个透视的列] AS <列名称>,

[最后一个透视的列] AS <列名称>,
FROM
(<生成数据的 SELECT 查询>)
AS <源查询的别名>
PIVOT
(
<聚合函数>(<要聚合的列>)
FOR
[<包含要成为列标题的值的列>]
IN ( [第一个透视的列], [第二个透视的列],
… [最后一个透视的列])
) AS <透视表的别名>
<可选的 ORDER BY 子句>;
insert into tbl_Student values(
‘S3′,’王五’,’K4        ‘,’政治’,53,’T4′,’赵老师’);
insert into tbl_Student values(
‘S1′,’ 张三 ‘,’K1        ‘,’数学’,61,’T1′,’张老师’);
insert into tbl_Student values(
‘S2′,’李四’,’K3        ‘,’英语’,88,’T3′,’李老师’);
insert into tbl_Student values(
‘S1′,’  张三 ‘,’K4        ‘,’政治’,77,’T4′,’赵老师’);
insert into tbl_Student values(
‘S2′,’李四’,’K4        ‘,’政治’,67,’T5′,’周老师’);
insert into tbl_Student values(
‘S3′,’王五’,’K2        ‘,’语文’,90,’T2′,’王老师’);
insert into tbl_Student values(
‘S3′,’王五’,’K1        ‘,’数学’,55,’T1′,’张老师’);
insert into tbl_Student values(
‘S1′,’ 张三 ‘,’K2        ‘,’语文’,81,’T2′,’王老师’);
insert into tbl_Student values(
‘S4′,’赵六’,’K2        ‘,’语文’,59,’T1′,’王老师’);
insert into tbl_Student values(
‘S1′,’ 张三 ‘,’K3        ‘,’英语’,37,’T3′,’李老师’);

select * from tbl_Student;

select 学生姓名,课程ID,成绩,教师ID,教师姓名,
max(case 课程名称 when ‘政治’ then 成绩 else 0 end) 政治,
max(case 课程名称 when ‘语文’ then 成绩 else 0 end) 语文,
max(case 课程名称 when ‘数学’ then 成绩 else 0 end) 数学
,max(case 课程名称 when ‘英语’ then 成绩 else 0 end) 英语
from tbl_Student group by 学生姓名,课程ID,成绩,教师ID,教师姓名;
—————————————————————-
select * from (select * from tbl_Student) a
pivot (max(成绩) for 课程名称 in (语文,数学,英语,政治)) b

—————————————————————————

CREATE TABLE [dbo].[tbl_列转行测试](
[UserID] [int] NULL,
[UserNo] [int] NULL,
[A] [int] NULL,
[B] [int] NULL,
[C] [int] NULL
) ON [PRIMARY]
insert into [tbl_列转行测试] values (1,1,11,22,33)
SELECT  USERID,USERNO,tType=attribute FROM (select * from tbl_列转行测试) a
UNPIVOT  (    value FOR attribute IN(A, B,C)  ) AS UPV



Copyright 2019. All rights reserved.

Posted 2014年8月26日 by ucjmh in category "sqlserver

Leave a Reply

Your email address will not be published. Required fields are marked *