行转列 -- pivot 的应用

碰上了行转列的问题，想起了 pivot，我们先看看 pivot 是什么：

 

PIVOT

 

通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合

 

旋转？不就是行转列吗？

 

再看看语法

 

 

以下是带批注的 PIVOT 语法。

SELECT <非透视的列>,

    [第一个透视的列] AS <列名称>,

    [第二个透视的列] AS <列名称>,

    ...

    [最后一个透视的列] AS <列名称>,

FROM

    (<生成数据的 SELECT 查询>)

    AS <源查询的别名>

PIVOT

(

    <聚合函数>(<要聚合的列>)

FOR

[<包含要成为列标题的值的列>]

    IN ( [第一个透视的列], [第二个透视的列],

    ... [最后一个透视的列])

) AS <透视表的别名>

<可选的 ORDER BY 子句>;

 

下面是例子：

 

KQRecord表

 

id	empid	flag	state	datetime	AuditingFlag	TimeID	Remark
3118	1	2	1	2010-9-1 12:00:00	False	1	NULL
3119	1	1	1	2010-9-1 13:00:00	False	2	NULL
3120	1	2	1	2010-9-1 17:30:00	False	2	NULL
3121	1	1	2	2010-9-1 18:00:00	False	3	NULL
3122	1	2	1	2010-9-1 21:30:00	False	3	NULL
3117	1	1	2	2010-9-1 9:00:00	False	1	NULL

 

KQTime表

 

id	TimeName
1	上午
2	下午
3	晚班

 

 

我要的结果：

 

 

 

 

存储过程：

CREATE PROCEDURE [dbo].[GET_KQRecord]

(

@EmpId int,

@ClassDate datetime

)

AS

BEGIN

select t1.timeid,TimeName,sbs,sbt,xbs,xbt from

(select TimeID,[1] as sbs,[2] as xbs from 

(select  TimeID,Flag,[state] from KQRecord where EmpID=@EmpId and [Datetime] between @ClassDate and dateadd(day,1,@ClassDate) )

as a pivot (max([state]) for Flag in ([1],[2])) as b) as t1

Inner  join

(select TimeID,[1] as sbt,[2] as xbt from

(select timeid,flag,[datetime] from kqrecord where empid=@EmpId and [datetime] between @ClassDate and dateadd(day,1,@ClassDate))

as c pivot (min([datetime]) for flag in ([1],[2])) as d) as t2

on t1.timeid=t2.timeid

Inner join

KQtime as t3

on t1.timeid=t3.Id

 

END

 

执行结果如上图所示，两个旋转后的结果集拼接而成，暂时就这样了～～

 

总结：pivot就是旋转结果集的，<包含要成为列标题的值的列>就是你想转的列，它的值就是<要聚合的列>