-2

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

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
user1445542
  • 41
  • 2
  • 4
  • Pivot is what you want, confused where table_1 fits in your question though – Tony Hopkinson Jun 10 '12 at 18:18
  • 1
    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

1 Answers1

0

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Sorry the first table is the one I need to join to. – user1445542 Jun 11 '12 at 01:12
  • @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