Sql Server 行列转换

今天碰到一个特殊的应用场景，收集到的客流数据，数据颗粒度到了单店单小时。

现在需要统计每个门店每天的客流数据，下面开搞。

 

1.原始数据：

 select * from yd_peopleflow 

结果：

 

2.先用简单的SQL实现7天的数据统计

select shopname,[2017-12-28],[2017-12-29],[2017-12-30],
[2018-01-01],[2018-01-02],[2018-01-03],[2018-01-04]
from 
(
    select shopNo,shopName,convert(varchar(100),StartDate,23) Date,Convert(decimal(28,0),isnull(InQty,0)) InQty
    from yd_peopleflow  where StartDate >=DATEADD(DAY,-7,GETDATE())) t1
pivot (
sum(InQty) for Date 
in([2017-12-28],[2017-12-29],[2017-12-30],[2018-01-01],[2018-01-02],[2018-01-03],[2018-01-04])
) t2

查询结果：

 

 3.封装成存储过程 

if exists(select 1 from sysobjects where name ='GetPeopleFlow' and xtype ='P' ) drop proc GetPeopleFlow
go
create proc GetPeopleFlow(@DateSetp varchar(20) ,@DateNumber int)
as
begin
    declare @startDate datetime,@enddate datetime,@dateSql varchar(500),@temp datetime,@Sql NVARCHAR (MAX)

    if(@DateSetp ='month')
    begin
        select @startDate =DATEADD(MONTH,-@DateNumber,cast(convert(varchar(10),getdate(),120) as datetime))
    end
    if(@DateSetp='day')
    begin
        select @startDate =DATEADD(DAY,-@DateNumber,cast(convert(varchar(10),getdate(),120) as datetime))
    end
    
    select @enddate =cast(convert(varchar(10),getdate(),120) as datetime)

    set @dateSql =''
    set @temp = @startdate
    while(@temp <=@enddate)
    begin
        set @dateSql = @dateSql +'['+ convert(varchar(100),@temp,23)+'],'
        set @temp = dateadd(day,1,@temp)
    end
    set @dateSql = left(@dateSql,len(@dateSql)-1)


    SET @sql ='select shopName ''门店'', '+@dateSql+'from (
    select shopNo,shopName,convert(varchar(100),StartDate,23) Date,Convert(decimal(28,0),isnull(InQty,0)) InQty
    from yd_peopleflow  where StartDate >=DATEADD(DAY,-7,GETDATE())) t1
    pivot (sum(InQty) for Date in('+@dateSql+')) t2 '

    EXEC(@sql);

end
go

执行存储过程：

 

GetPeopleFlow 'day',3

结果：

 

到此，业务结束，目标达成，前端只要执行存储过程就行。

当然如果C#或者Java可以后台直接拼装成代码，就不需要数据库端拼接数据库语句了。