Как мога да превърна куп редове в обобщени колони, БЕЗ да използвам завъртане в SQL Server 2005?

Ето сценария: Имам таблица, която записва user_id, module_id и датата/часа, когато модулът е бил прегледан.

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

Трябва да получа набор от резултати, който има user_id като 1-ва колона и след това колона за всеки модул. След това данните за реда са user_id и броят пъти, когато потребителят е прегледал всеки модул.

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

Първоначално мислех, че мога да направя това с PIVOT, но без зарове; базата данни е преобразувана SQL Server 2000 DB, която работи в SQL Server 2005. Не мога да променя нивото на съвместимост, така че pivot не работи.

Другата уловка е, че модулите ще варират и не е възможно да пренаписвате заявката всеки път, когато се добавя или премахва модул. Това означава, че не мога да кодирам твърдо модулите, защото не знам предварително кои ще бъдат инсталирани и кои не.

Как мога да постигна това?


sql sql-server pivot sql-server-2005
person cdeszaq    schedule 27.04.2010    source източник

Отговори (4)


arrow_upward
7
arrow_downward

PIVOT може да се симулира с CASE и 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]

Разбира се, това не работи, ако модулите варират (както е посочено във въпроса), но тогава 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

Обърнете внимание, че това може да е уязвимо за sql инжектиране, ако не може да се вярва на данните за идентификатора на модула.

person Daniel Renshaw    schedule 27.04.2010
comment
Има ли начин да направите този блок от отчети за сума/случай динамичен? Инсталираните модули не са фиксирани, така че дискретното изброяване на модулите не е възможно. - person cdeszaq; 27.04.2010
comment
Забелязах това точно преди да публикувате. PIVOT има същия проблем - трябва да кодирате твърдо колоните, които искате отпред. - person Daniel Renshaw; 27.04.2010
comment
Има динамичен начин за получаване на колоните, илюстриран тук: stackoverflow.com/questions/2344590/, но това не работи за мен, защото не мога да използвам pivot . Има ли начин да смесите двете? - person cdeszaq; 27.04.2010
comment
Да, може динамично да генерира sql, както в моя редактиран отговор, но не ми харесва! - person Daniel Renshaw; 27.04.2010

arrow_upward
0
arrow_downward

С помощта на MySQL направих това:

  1. Вашите данни са копирани в Log_Table.sql
  2. create table Log (User_ID mediumint, Module_ID CHAR(5), dte CHAR(10));
  3. load data infile 'Log_Table.sql' INTO TABLE Log FIELDS TERMINATED BY ',';

  4. Шарнирен болт:

    select User_ID AS 'USER',  sum(case
Module_ID WHEN 'red'   then 1 else 0
END) AS 'red', 

sum(case Module_ID WHEN 'green' then 1
else 0 END) AS 'green', 

sum(case Module_ID WHEN 'blue'  then 1
else 0 END) AS 'blue', 

sum(case Module_ID WHEN 'white' then 1
else 0 END) AS 'white'

from Log 

Group By User_ID;

> +------+------+-------+------+-------+ 
> | USER | red  | green | blue | white |
> +------+------+-------+------+-------+ 
> |    1 |    2 |     1 |    2 |     0 |
> |    2 |    0 |     1 |    1 |     0 |
> |    3 |    1 |     2 |    1 |     0 |
> |    4 |    0 |     0 |    0 |     1 |
> +------+------+-------+------+-------+ 
> 4 rows in set (0.00 sec)

Надявам се това да помогне.

person okaygo    schedule 27.04.2010

arrow_upward
-1
arrow_downward

Вярвам, че характерни функции са това, което искате.

person Carl Manaster    schedule 27.04.2010

arrow_upward

arrow_downward

person    schedule
comment
Инсталираните модули не са фиксирани, така че дискретно изброяване на модулите не е възможно. Има ли динамичен начин да направите това? - person cdeszaq; 27.04.2010