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

Pivot is what you want, confused where table_1 fits in your question though — Tony Hopkinson, Jun 10 '12 at 18:18
Where does Table_1 come into it? From your limited question, it looks like only the data from Table_2 is required (oops, browser delayed showing Tony's comment) — freefaller, Jun 10 '12 at 18:21
Also what do you mean by "best"? And if customer 1 hit status 1 on Jan 12 and on Feb 15, which one do you want? Latest? — Aaron Bertrand, Jun 10 '12 at 19:46

score 0 · Answer 1 · answered Jun 10 '12 at 19:58

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.

@user1445542 in that case you need to re-write your OP (original post), because what you're asking doesn't make sense... the fact that all responses so far question the point of Table_1 highlights that. — freefaller, Jun 11 '12 at 07:49

Transpose rows to columns

1 Answers1

Linked