如何创建动态数据透视表SQL Server 2008 [英] How to create a dynamic pivot table SQL Server 2008

问题描述

这就是我在SQL Server 2008数据库中所拥有的，我正在尝试数据透视表，但是很难使其正常工作

That is what I have in my SQL Server 2008 database, I am trying pivot table but having hard time to get it to work

Username     Date     hours
--------------------------------------
John       Feb 1995    45
John       Feb 1995    16      
Nancy      March 1998  25
John       May 2001   35.5
Peter      Feb 1995    46
Bill       May 2001    48
Bill       Feb 1995    56

我需要得到以下结果:

UserName   Feb 1995   March 1998   May 2001
--------   --------   ----------   ---------
John        61                      35.5
Nancy                    25
Peter       46
Bill        56                      48
--------------------------------------------
Total       163          25         83.5

基本上，我需要从date列值(1995-10-01)中获取所有值，并使其成为列的标题，这是动态的，取决于表中有多少个值.

Basically I need take all the values from the date column values (1995-10-01) and make it the title of the columns, this is dynamic depends on how many values in the table.

我们非常感谢您的帮助

推荐答案

首先，一种实现方法:

create table #tab (
            Username nvarchar(100),
            [Date] date,
            [hours] numeric(8,1)
        )

insert #tab values 
    ('John', '1995-02-01', 45),
    ('John', '1995-02-01', 16),
    ('Nancy', '1998-03-01', 25),
    ('John', '2001-05-01', 35.5),
    ('Peter', '1995-02-01', 46),
    ('Bill', '2001-05-01', 48),
    ('Bill', '1995-02-01', 56)


select  pvt.*
    from (
        select  Username, 
                datename(month, [Date]) + ' ' + convert(nvarchar(20), datepart(year, [Date])) [Date], 
                [hours] 
            from #tab
        ) t
    pivot (
        SUM([hours])
        for [Date] in ([February 1995], [March 1998], [May 2001])
        ) pvt

drop table #tab

现在，您可能需要做的方式:

Now, the way you'll probably need to do it:

create table #tab (
            Username nvarchar(100),
            [Date] date,
            [hours] numeric(8,1)
        )

insert #tab values 
    ('John', '1995-02-01', 45),
    ('John', '1995-02-01', 16),
    ('Nancy', '1998-03-01', 25),
    ('John', '2001-05-01', 35.5),
    ('Peter', '1995-02-01', 46),
    ('Bill', '2001-05-01', 48),
    ('Bill', '1995-02-01', 56)

declare @columns nvarchar(max) = ''
declare @sql nvarchar(max) = ''
declare @delim nvarchar(10) = ''

select  @columns = @columns + @delim + '[' + x.[Date] + ']',
        @delim = ', '
    from (  
        select  datename(month, t.[Date]) + ' ' + convert(nvarchar(20), datepart(year, t.[Date])) [Date],
                datepart(year, t.[Date]) [Year],
                datepart(month, t.[Date]) [Month]
            from #tab t
        ) x
    group by x.[Date]
    order by MAX(x.[Year]), MAX(x.[Month])


select @columns

set @sql = '
    select  pvt.Username,
            ' + @columns + '
        from (
            select  Username, 
                    datename(month, [Date]) + '' '' + convert(nvarchar(20), datepart(year, [Date])) [Date], 
                    [hours] 
                from #tab
            ) t
        pivot (
            SUM([hours])
            for [Date] in (' + @columns + ')
            ) pvt
    '

exec(@sql)

drop table #tab

问题是PIVOT不允许您动态指定结果列...您必须知道要进行此操作的列中的内容.解决方案是使用不同的列创建动态SQL，然后基于该列构建数据透视表语句.

The problem is that PIVOT doesn't allow you to dynamically specify the resulting columns... you have to know what's in the column you're pivoting on to do it. The solution is to create dynamic SQL with the distinct columns, and then build a pivot statement based on that.

这篇关于如何创建动态数据透视表SQL Server 2008的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！