将行转置为列 [英] Transpose rows to columns

问题描述

将行转换为列的最佳方法是什么.

What is the best way to transpose rows to columns.

表_1

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

表_2

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

视图/表格输出

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

谢谢

推荐答案

假设您知道只有四种可能的状态值，并且您想要任何 cust_id/status 组合的最新日期:

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;

结果:

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.

这篇关于将行转置为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！