sql server 2008 r2 - Transpose rows to columns

Question

What is the best way to transpose rows to columns.

Table_1

Cust_id  field1  field2 ...
-------  ------  ------

Table_2

Cust_id  status  date_changed
-------  ------  ------------

View/table output

Cust_id  status1      status2        status3      status4
-------  -----------  -------------  -----------  -----------
1        01-jan-2011  05-April-2011  06-Dec-2012  30-Dec-2012

Thanks

Answer 1

Assuming you know there are only four possible status values, and that you want the latest date for any cust_id/status combination:

DECLARE @table_2 TABLE(Cust_id INT, status INT, date_changed DATE);

INSERT @table_2 VALUES
(1,1,SYSDATETIME()),
(2,1,SYSDATETIME()),
(3,1,DATEADD(DAY, 1, SYSDATETIME())),
(3,1,SYSDATETIME()),
(1,2,SYSDATETIME());

SELECT cust_id, 
    status1 = [1], status2 = [2], 
    status3 = [3], status4 = [4]
FROM 
(
  SELECT cust_id, status, date_changed
  FROM @table_2
) AS t
PIVOT (MAX(date_changed) 
FOR [status] IN ([1],[2],[3],[4])) AS p
ORDER BY cust_id;

Results:

cust_id status1    status2    status3 status4
------- ---------- ---------- ------- -------
1       2012-06-10 2012-06-10 NULL    NULL
2       2012-06-10 NULL       NULL    NULL
3       2012-06-11 NULL       NULL    NULL

That is the standard way to do this. There may be better approaches depending on your goals and/or whether you know the number of potential status values beforehand or want to only show columns for those status values that exist. If any of those situations apply, please update the question.