SQL Server UNPIVOT table by combining two rows based on condition, using a SINGLE unpivot

Question

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.

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

SqlFiddle here

Thank you!

Answer 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.

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

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

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.

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.

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.

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