如何在不使用SQL Server 2005中的数据透视表的情况下将一堆行变成聚合的列? [英] How can I turn a bunch of rows into aggregated columns WITHOUT using pivot in SQL Server 2005?

问题描述

这是场景: 我有一个表，其中记录了user_id，module_id和查看模块的日期/时间.

例如

Table: Log
------------------------------
User_ID  Module_ID   Date
------------------------------
1       red         2001-01-01
1       green       2001-01-02
1       blue        2001-01-03
2       green       2001-01-04
2       blue        2001-01-05
1       red         2001-01-06
1       blue        2001-01-07
3       blue        2001-01-08
3       green       2001-01-09
3       red         2001-01-10
3       green       2001-01-11
4       white       2001-01-12

我需要获取一个具有user_id作为第一列的结果集，然后是每个模块的一列.然后，行数据是user_id和用户查看每个模块的次数计数.

例如

---------------------------------
User_ID  red green   blue    white
---------------------------------
1       2   1       2       0
2       0   1       1       0
3       1   2       1       0
4       0   0       0       1

我最初以为我可以用PIVOT做到这一点，但没有骰子.该数据库是在SQL Server 2005中运行的经过转换的SQL Server 2000 DB.由于无法更改兼容性级别，因此可以进行数据透视.

另一个问题是模块会有所不同，并且每次添加或删除模块时都无法重新编写查询.这意味着我无法在模块中进行硬编码，因为我事先不知道将要安装和不安装哪些模块.

我该怎么做?

解决方案

可以使用CASE和GROUP BY模拟PIVOT

select
    [user_id],
    sum(case when [Module_ID] = 'red' then 1 else 0 end) as red,
    sum(case when [Module_ID] = 'green' then 1 else 0 end) as green,
    sum(case when [Module_ID] = 'blue' then 1 else 0 end) as blue,
    sum(case when [Module_ID] = 'white' then 1 else 0 end) as white
from [log]
group by
    [user_id]

当然，如果模块有所不同(如问题所述)，这将不起作用，但是PIVOT存在相同的问题.

动态生成一些sql可以解决此问题，但是这种解决方案有点难闻！

declare @sql nvarchar(max)

set @sql = '
select
    [user_id],'

select @sql = @sql + '
    sum(case when [Module_ID] = ''' + replace([Module_ID], '''','''''') + ''' then 1 else 0 end) as [' + replace([Module_ID], '''','') + '],'
from (select distinct [Module_ID] from [log]) as moduleids

set @sql = substring(@sql,1,len(@sql)-1) + '
from [log]
group by
    [user_id]
'
print @sql
exec sp_executesql @sql

请注意，如果模块ID数据不能被信任，则这很容易受到sql-injection的攻击.<​​/p>

Here is the scenario: I have a table that records the user_id, the module_id, and the date/time the module was viewed.

eg.

Table: Log
------------------------------
User_ID  Module_ID   Date
------------------------------
1       red         2001-01-01
1       green       2001-01-02
1       blue        2001-01-03
2       green       2001-01-04
2       blue        2001-01-05
1       red         2001-01-06
1       blue        2001-01-07
3       blue        2001-01-08
3       green       2001-01-09
3       red         2001-01-10
3       green       2001-01-11
4       white       2001-01-12

I need to get a result set that has the user_id as the 1st column, and then a column for each module. The row data is then the user_id and the count of the number of times that user viewed each module.

eg.

---------------------------------
User_ID  red green   blue    white
---------------------------------
1       2   1       2       0
2       0   1       1       0
3       1   2       1       0
4       0   0       0       1

I was initially thinking that I could do this with PIVOT, but no dice; the database is a converted SQL Server 2000 DB that is running in SQL Server 2005. I'm not able to change the compatibility level, so pivot is out.

The other catch is that the modules will vary, and it isn't feasible to re-write the query every time a module is added or removed. This means that I can't hard-code in the modules because I don't know in advance which will and will not be installed.

How can I accomplish this?

解决方案

PIVOT can be simulated with CASE and GROUP BY

select
    [user_id],
    sum(case when [Module_ID] = 'red' then 1 else 0 end) as red,
    sum(case when [Module_ID] = 'green' then 1 else 0 end) as green,
    sum(case when [Module_ID] = 'blue' then 1 else 0 end) as blue,
    sum(case when [Module_ID] = 'white' then 1 else 0 end) as white
from [log]
group by
    [user_id]

Of course this doesn't work if the modules vary (as stated in the question) but then, PIVOT has the same problem.

Dynamically generating some sql overcomes this problem but this solution smells a bit!

declare @sql nvarchar(max)

set @sql = '
select
    [user_id],'

select @sql = @sql + '
    sum(case when [Module_ID] = ''' + replace([Module_ID], '''','''''') + ''' then 1 else 0 end) as [' + replace([Module_ID], '''','') + '],'
from (select distinct [Module_ID] from [log]) as moduleids

set @sql = substring(@sql,1,len(@sql)-1) + '
from [log]
group by
    [user_id]
'
print @sql
exec sp_executesql @sql

Note that this may be vulnerable to sql-injection if the module id data can't be trusted.

这篇关于如何在不使用SQL Server 2005中的数据透视表的情况下将一堆行变成聚合的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！