SQL Server UNPIVOT表由两行基于条件组合而成，使用单个非主元。

I have a big query which gives a table result looking like this:

我有一个很大的查询，结果是这样的:

[Date]          [Type]  TC1   TC2    TC3
..........................................
'2014-01-01'    T       11    22     33
'2014-01-01'    C       44    55     66
'2014-01-02'    T       111   222    333
'2014-01-02'    C       444   555    666
'2014-01-03'    T       1111  2222   3333
'2014-01-04'    C       4444  5555   6666

I want to make it look like this:

我想让它看起来像这样:

[Date]          Cntr      TValue      CValue
............................................
'2014-01-01'    TC1       11          44
'2014-01-01'    TC2       22          55
'2014-01-01'    TC3       33          66
'2014-01-02'    TC1       111         444
'2014-01-02'    TC2       222         555
'2014-01-02'    TC3       333         666
'2014-01-03'    TC1       1111        4444
'2014-01-03'    TC2       2222        5555
'2014-01-03'    TC3       3333        6666

I managed to do this by unpivoting two times and then join the results. The query I made is this:

我试着去旋转两次，然后加入结果。我的查询是这样的:

SELECT A.[Date],A.Cntr,A.TValue,B.CValue
FROM
(
    SELECT [Date],Cntr,TValue
    FROM TB 
    UNPIVOT
    (
    TValue
    FOR Cntr IN (TC1,TC2,TC3)
    ) u
    WHERE  [Type] = 'T' AND u.[Type] = 'T'
) A
JOIN
(
    SELECT [Date],Cntr,CValue
    FROM TB 
    UNPIVOT
    (
    CValue
    FOR Cntr IN (TC1,TC2,TC3)
    ) u
    WHERE  [Type] = 'C' AND u.[Type] = 'C'
) B
ON B.[Date] = A.[Date] AND A.[Cntr] = B.[Cntr]

My problem is that TB table comes from a big and time consuming query and, given the fact that it will be called thousands of times during my stored procedure, I dont want to waste precious time by inserting its many rows in a temporary table.

我的问题是TB表来自一个庞大而耗时的查询，而且考虑到它在存储过程中将被调用数千次，我不想浪费宝贵的时间将它的许多行插入临时表中。

Is there a way to solve this by using a single unpivot, or using a better approach ? Also, this has to run on SQLServer >= 2005

是否有一种方法可以通过使用单个unpivot或者使用更好的方法来解决这个问题?而且，这必须在SQLServer >= 2005上运行

SqlFiddle here

SqlFiddle这里

Thank you!

谢谢你！

2 个解决方案

#1

Since (Date/Type) are constrained to be unique in TB, then you can UNPIVOT and then PIVOT to avoid the join, MAX(Value) is an arbitrary aggregation, since there can only be one value anyway.

由于(Date/Type)在TB中被限制为惟一的，因此您可以取消PIVOT并转向以避免连接，MAX(Value)是一个任意的聚合，因为无论如何只能有一个值。

SELECT  pvt.Date,
        pvt.Cntr,
        TValue = pvt.T,
        CValue = pvt.C
FROM    TB
        UNPIVOT (Value FOR Cntr IN (TC1, TC2, TC3)) AS upvt
        PIVOT (MAX(Value) FOR [Type] IN (C, T)) AS pvt;

Example on SQL Fiddle

示例SQL小提琴

This results in half the number of reads (as you'd probably expect). IO Statistics show:

这将导致半数的读取(正如您可能预期的那样)。IO统计数据显示:

UNPIVOT/PIVOT

透视/主

Table 'TB'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

表“结核”。扫描计数1，逻辑读取1，物理读取0，读前读取0,lob逻辑读取0,lob物理读取0,lob读取前读取0。

UNPIVOT/UNPIVOT/JOIN

透视/透视/加入

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

表“工作台”。扫描计数0，逻辑读取0，物理读取0，读前读取0,lob逻辑读取0,lob物理读取0,lob读取前读取0。

Table 'TB'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

表“结核”。扫描数2，逻辑读2，物理读0，读读0,lob逻辑读0,lob物理读0,lob读读0。

And a simpler execution plan (UNPIVOT/PIVOT on top):

和一个更简单的执行计划(上面的UNPIVOT/PIVOT):

#2

One way to do this is using cross apply and conditional aggregation:

一种方法是使用交叉应用和条件聚合:

select [date], [cntr],
       max(case when [type] = 'T' then val end) as TValue,
       max(case when [type] = 'C' then val end) as CValue
from (<your query here>) t cross apply
     (values ([date], [type], 'tc1', tc1),
             ([date], [type], 'tc2', tc2),
             ([date], [type], 'tc3', tc3)
     ) vals([date], [type], cntr, val)
group by [date], [cntr];

EDIT:

编辑:

I missed the SQL Server 2005 condition. The syntax before values() is:

我错过了SQL Server 2005的条件。值()之前的语法是:

select [date], [cntr],
       max(case when [type] = 'T' then val end) as TValue,
       max(case when [type] = 'C' then val end) as CValue
from (<your query here>) t cross apply
     (select [date], [type], 'tc1', tc1 union all
      select [date], [type], 'tc2', tc2 union all
      select [date], [type], 'tc3', tc3
     ) vals([date], [type], cntr, val)
group by [date], [cntr];