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
如果觉得现在晚了,那么现在还来得及。