privot函数使用

语法：

table_source

PIVOT(

聚合函数（value_column）

FOR pivot_column

IN(<column_list>)

)

将列转化为行

写个小示例：比如按人统计该月份的考勤小时数

USE [test]

GO

/****** Object:  Table [dbo].[KaoQin]    Script Date: 07/06/2017 15:44:34 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[KaoQin](

    [autoId] [int] IDENTITY(1,1) NOT NULL,

    [userName] [nvarchar](50) NULL,

    [dt] [datetime] NULL,

    [hour] [int] NULL,

 CONSTRAINT [PK_KaoQin] PRIMARY KEY CLUSTERED

(

    [autoId] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

然后新增一点儿测试数据

declare @count int

set @count=5

declare @i int

set @i=0

declare @riqi datetime

set @riqi=GETDATE()

while  @i<@count

begin

    set @riqi=dateadd(day,1,@riqi)

    insert into dbo.KaoQin values('王二',@riqi, cast(RAND()*10 as int))

    set @i=@i+1

end

最后数据是这样的

传统的办法当然也可以解决，如下

select

SUM( case  when userName='张三' then hour else 0 end )as '张三',

SUM( case when userName='李四' then hour else 0 end ) as '李四',

SUM( case when userName='王二' then hour else 0 end ) as '王二'

 from dbo.KaoQin

哪些下面重点来了我们用pivot

巴特西

privot函数使用

最新文章

热门文章