SQL Server 2012 垂直数据到水平

问题描述：

我有一个 SQL Server 2012 表，我需要用多列轮换它.我已经检查了其他问题的几个答案，例如(SQL Server 2008 垂直数据到水平)，但没有一个对我的情况有效.我有临时表#temp2"，我想同时选择和过滤它.下面是#temp2表的内容

I have a SQL Server 2012 table that I need to rotate with multiple columns. I have checked several answers on other questions like (SQL Server 2008 vertical data to horizontal) but none has worked for my case. I have temp table "#temp2" which i want to select and filter in same time. Here are the contents of the #temp2 table

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

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

I want to transform this table like this one below

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

表格将从垂直旋转到水平，对于同一位贷款员，将有两行，一是成员未完成的一列.

The table will be rotated from Vertical to Horizontal and for same Loan Officer there will be two rows one for Outstanding one for Members.

感谢您的帮助.

答

你可以像这样使用条件聚合:

You can use conditional aggregation like this:

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;

SQL Server 2012 垂直数据到水平

相关推荐