SQL Server 2012垂直数据到水平数据

Question

我有一个SQL Server 2012表，需要用多列轮换。我已经检查了其他问题的几个答案，比如(SQLServer2008垂直数据到水平数据(，但没有一个对我的情况有效。我有一个临时表"#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

表格将从垂直旋转到水平，对于同一贷款官员，将有两行，其中一行为成员的未偿贷款。

谢谢你的帮助。

Answer 1

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

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;

Answer 2

Gordon Linof查询很接近，但没有在表中显示成员，但这就是我解决问题的方法：

select * from 
(select [Loan Officer],
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,
max(case when daysdelay = 'Days 31-60' then outstanding end) as days_31_60,
max(case when daysdelay = 'Days 61-90' then outstanding end) as days_61_90,
max(case when daysdelay = 'Days 91-180' then outstanding end) as days_91_180,
max(case when daysdelay = 'Days over 180' then outstanding end) as days_above_190
from (select t.*,
row_number() over (partition by [Loan Officer] order by members desc) as seqnum
from #temp2 t
) t
group by [Loan Officer]
union all
select [Loan Officer],
max(case when daysdelay = 'Days 0' then members end) as days_0,
max(case when daysdelay = 'Days 1-30' then members end) as days_1_30,
max(case when daysdelay = 'Days 31-60' then members end) as days_31_60,
max(case when daysdelay = 'Days 61-90' then members end) as days_61_90,
max(case when daysdelay = 'Days 91-180' then members end) as days_91_180,
max(case when daysdelay = 'Days over 180' then members end) as days_above_190
from (select t.*,
row_number() over (partition by [Loan Officer] order by members desc) as seqnum
from #temp2 t
) t
group by [Loan Officer]
) x where 1=1
order by [Loan Officer]