语法：

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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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=5declare @i int set @i=0declare @riqi datetimeset @riqi=GETDATE()while  @i<@countbegin        set @riqi=dateadd(day,1,@riqi)        insert into dbo.KaoQin values('王二',@riqi, cast(RAND()*10 as int))    set @i=@i+1end

 最后数据是这样的

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

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