基于列值分割数据的SQL查询

I have the following data:

我有以下数据:

SR_ID   OPEN_DATE   OPEN_WEEK   PRIORITY    TTRespond
------- ----------- ----------- ----------- -------------
24720   01/10/2014  40          P2 - High   3.867066667
24437   11/09/2014  37          P2 - High   418.1992333
24007   04/08/2014  32          P1 - Urgent 12571.28308
24628   25/09/2014  39          P2 - High   3.0407
24446   12/09/2014  37          P2 - High   2694.122933
24420   10/09/2014  37          P3 - Normal 
24479   15/09/2014  38          P2 - High   90.56748333
24924   15/10/2014  42          P3 - Normal 26.51546667
24706   01/10/2014  40          P2 - High

I am trying to write a query that will return the data in the last column (TTRespond) in separate columns based on the value in OPEN_WEEK and PRIORITY.

我正在尝试编写一个查询，该查询将根据OPEN_WEEK中的值和优先级在单独的列(ttresponse)中返回最后一列中的数据。

I would like to return the data like this:

我想返回如下数据:

Priority    P1 - Urgent P1 - Urgent P1 - Urgent P1 - Urgent P1 - Urgent P1 - Urgent
----------- ----------- ----------- ----------- ----------- ----------- -----------
Week #      31          32          33          34          35          36

I do not want to summarise the data (i.e. Count Average etc), I simply want to return all rows that match the two criteria. I achieved the result using an array based formula in Excel, but it is very slow and cannot cope with the source record set (only 25,000 rows).

我不想总结数据(例如，Count Average等)，我只想返回符合这两个条件的所有行。我在Excel中使用了一个基于数组的公式实现了这个结果，但是它非常慢，无法处理源记录集(只有25000行)。

Any help will be greatly appreciated.

如有任何帮助，我们将不胜感激。

Thanks

谢谢

1 个解决方案

#1

It's not entirely clear what you're asking. But supposing you're looking for an output something like this, where the priority/week number are columns, and all the possible matching values are listed out in rows:

你问的问题并不完全清楚。但假设你在寻找一个输出，比如，优先级/周数是列，所有可能匹配的值都列在行中:

P2 - High, Week 37 | P2 - High, Week 38 | P2 - High, Week 39 | P2 - High, Week 40
---------------------------------------------------------------------------------
418.1992333        | 90.56748333        | 3.0407             | 3.867066667
2694.122933        |                    |                    |

This kind of "row/column inversion" is called a pivot table. Simple pivot tables can be done with clever usage of group by and max clauses in the following fashion in SQL Server:

这种“行/列倒置”称为主表。简单的pivot表可以在SQL Server中巧妙地使用group by和max子句，方式如下:

;with T as (
    select
        row_number() over (partition by priority, open_week order by open_date) ix
        ,T.*
    from MyTable T
    where TTrespond is not null
), R as (
    select distinct ix from T
)
select
    R.ix
    ,max(case when [OPEN_WEEK]=37 and T.ix=R.ix and [PRIORITY]='P2 - High' then TTRespond end) as [P2 - High, Week 37]
    ,max(case when [OPEN_WEEK]=38 and T.ix=R.ix and [PRIORITY]='P2 - High' then TTRespond end) as [P2 - High, Week 38]
    ,max(case when [OPEN_WEEK]=39 and T.ix=R.ix and [PRIORITY]='P2 - High' then TTRespond end) as [P2 - High, Week 39]
    ,max(case when [OPEN_WEEK]=40 and T.ix=R.ix and [PRIORITY]='P2 - High' then TTRespond end) as [P2 - High, Week 40]
from T, R
group by R.ix

The possible values can be expanded by adding more column combinations modeled after the examples above.

可以根据上面的示例添加更多的列组合来扩展可能的值。

#1