时间戳

首页 > 解决方案 > SQL Server 2012 垂直数据到水平

sql-server - SQL Server 2012 垂直数据到水平

问题描述

我有一个需要旋转多列的 SQL Server 2012 表。我已经检查了其他问题的几个答案，例如（SQL Server 2008 垂直数据到水平），但没有一个适合我的情况。我有临时表“#temp2”，我想同时选择和过滤它。以下是#temp2 表的内容

Outstanding     Members     Loan Officer    DaysDelay
------------------------------------------------------------
205130.62        165        Kevin           Days 0
 61259.68         42        Kevin           Days 1-30
   141.88          2        Kevin           Days 31-60
   562.23          1        Kevin           Days 91-180
  8233.3           4        Kevin           Days over 180
206648.85        153        Alan            Days 0
 39903.47         40        Alan            Days 1-30
  3263.66          2        Alan            Days 91-180
 15628.94          8        Alan            Days over 180
230604.98        155        Kate            Days 0
 58086.31         39        Kate            Days 1-30
 22616.34          2        Kate            Days 31-60
  1310.46          1        Kate            Days 61-90
  3645.12          3        Kate            Days 91-180
  3466.41          3        Kate            Days over 180

我想把这张桌子改成下面这张

Loan Officer    Days 0      Days 1-30   Days 31-60      Days 61-90      Days 91-180 Days over 180
Kevin           205130.62   61259.68    141.88          NULL            562.23      8233.3
Kevin           165         42          2               NULL            1           4
Alan            206648.85   39903.47    NULL            NULL            3263.66     15628.94
Alan            153         40          NULL            NULL            2           8
Kate            230604.98   58086.31    22616.34        1310.46         3645.12     3466.41
Kate            155         39          2               1               3           3

该表将从垂直旋转到水平，并且对于同一信贷员，将有两行，一排为杰出会员，一排为会员。

谢谢您的帮助。

标签： sql-servertsqlpivotpivot-table

解决方案

您可以像这样使用条件聚合：

select loanofficer,
       max(case when daysdelay = 'Days 0' then outstanding end) as days_0,
       max(case when daysdelay = 'Days 1-30' then outstanding end) as days_1_30,
       . . .   -- for the rest of the columns
from (select t.*,
             row_number() over (partition by loanofficer order by members desc) as seqnum
      from #temp2 t
     ) t
group by loanofficer, seqnum
order by loanofficer, seqnum;

推荐阅读