SQL Pivot Tables - Copy Excel Functionality

Question

Let's say I have a table like this:

Task   Type   Variable   Hours    Duration
One    A      X          10       5
One    A      Y          40       15
One    B      X          100      29
Two    A      X          5        2
Two    B      X          15       9
Two    A      Y          60       17
Three  A      Y          18       5

Where the combination of task-type-variable makes each row unique.

How can I get a pivot table like the following:

                            X        Y
One     A      Hours        10       40
               Duration     5        15
One     B      Hours        100      0     
               Duration     29       0
Two     A      Hours        5        60
               Duration     2        17
Two     B      Hours        15       0
               Duration     9        0
Three   A      Hours        0        18
               Duration     0        5

Is this even possible in SQL? I know Excel can do this.

Answer 1

This is a really an UNPIVOT and a PIVOT. The following code achieves the desired results in a single query.

DECLARE @t TABLE (
    Task     varchar(5),
    Type     char(1),
    Variable char(1),
    Hours    int,
    Duration int
    ) 

INSERT INTO @t
    VALUES
        ('One',   'A', 'X',  10,  5),
        ('One',   'A', 'Y',  40, 15),
        ('One',   'B', 'X', 100, 29),
        ('Two',   'A', 'X',   5,  2),
        ('Two',   'B', 'X',  15,  9),
        ('Two',   'A', 'Y',  60, 17),
        ('Three', 'A', 'Y',  18,  5)

SELECT
        P.Task,
        P.Type,
        CAST(P.Property AS varchar(8)) AS Property,
        COALESCE(P.X, 0) AS X,
        COALESCE(P.Y, 0) AS Y
    FROM @t AS T
    UNPIVOT (
        Value FOR Property IN (
            Hours,
            Duration
            )
        ) AS U
    PIVOT (
        SUM(Value) FOR Variable IN (
            X,
            Y
            )
        ) AS P

This yields the following results.

Task  Type Property X           Y
----- ---- -------- ----------- -----------
One   A    Duration 5           15
One   A    Hours    10          40
One   B    Duration 29          0
One   B    Hours    100         0
Three A    Duration 0           5
Three A    Hours    0           18
Two   A    Duration 2           17
Two   A    Hours    5           60
Two   B    Duration 9           0
Two   B    Hours    15          0

As you can see, the Hours and Duration are flipped. I don't think there is any way to force an order using PIVOT alone. This could easily be remedied by joining to another table with the Property value with an associated sort order, as long as you had some other way to ensure the other columns sorted correctly first.