Binaryworld - Another pivot table example ... [ SQL Server -> T-SQL ]

CREATE TABLE #t1 ( foo_type  int , bar_type  int , timestamp_ts datetime );

INSERT #t1 VALUES( 0 , 1000 , '1/12/00 00:00:00' );
INSERT #t1 VALUES( 0 , 1001 , '1/12/00 00:00:00' );
INSERT #t1 VALUES( 0 , 1002 , '1/12/00 00:00:00' );
INSERT #t1 VALUES( 1 , 1000 , '1/13/00 00:00:00' );
INSERT #t1 VALUES( 1 , 1001 , '1/13/00 00:00:00' );
INSERT #t1 VALUES( 1 , 1002 , '1/13/00 00:00:00' );
INSERT #t1 VALUES( 2 , 1000 , '1/14/00 00:00:00' );
INSERT #t1 VALUES( 2 , 1001 , '1/14/00 00:00:00' );
INSERT #t1 VALUES( 2 , 1002 , '1/14/00 00:00:00' );
INSERT #t1 VALUES( 3 , 1000 , '1/15/00 00:00:00' );

SELECT bar_type , foo_type , CONVERT( varchar , timestamp_ts , 101 ) AS timestamp_ts
FROM #t1
ORDER BY bar_type , foo_type DESC , timestamp_ts;
-- Sample Output
/*
bar_type  foo_type  timestamp_ts
----------- ----------- ------------------------------
    1000      3 01/15/2000
    1000      2 01/14/2000
    1000      1 01/13/2000
    1000      0 01/12/2000
    1001      2 01/14/2000
    1001      1 01/13/2000
    1001      0 01/12/2000
    1002      2 01/14/2000
    1002      1 01/13/2000
    1002      0 01/12/2000
*/

-- Desired Output:
-- Maximum foo_type for each bar_type & the timestamp values ( upto 4 )
SELECT t.bar_type ,
    MAX( t.foo_type ) AS foo_type ,
    MIN( CASE t.ts_cnt WHEN 1 THEN t.timestamp_ts END ) AS time1_ts ,
    MIN( CASE t.ts_cnt WHEN 2 THEN t.timestamp_ts END ) AS time2_ts ,
    MIN( CASE t.ts_cnt WHEN 3 THEN t.timestamp_ts END ) AS time3_ts ,
    MIN( CASE t.ts_cnt WHEN 4 THEN t.timestamp_ts END ) AS time4_ts
FROM (
SELECT foo_type , bar_type , timestamp_ts ,
    -- Get count of each timestamp value within a bar_type
    -- This is used to generate the columns later
    ( SELECT COUNT( * ) FROM #t1 AS t2
     WHERE t2.bar_type = t1.bar_type And
        t2.timestamp_ts <= t1.timestamp_ts ) AS ts_cnt
FROM #t1 AS t1
) AS t
GROUP BY t.bar_type
ORDER BY t.bar_type;
/*
bar_type foo_type time1_ts        time2_ts        time3_ts        time4_ts
-------- -------- ----------------------- ----------------------- ----------------------- -----------------------
  1000    3 2000-01-12 00:00:00.000 2000-01-13 00:00:00.000 2000-01-14 00:00:00.000 2000-01-15 00:00:00.000
  1001    2 2000-01-12 00:00:00.000 2000-01-13 00:00:00.000 2000-01-14 00:00:00.000 NULL
  1002    2 2000-01-12 00:00:00.000 2000-01-13 00:00:00.000 2000-01-14 00:00:00.000 NULL
*/