CODEWITHSUNDEEP
sql-servert-sqlpivotpivot-table
aarriiaann
aarriiaann

Reputation: 76

SQL Server 2012 Vertical data to Horizontal

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.

Thank you for your help.

Upvotes: 0

Views: 79

Answers (2)

aarriiaann
aarriiaann

Reputation: 76

Gordon Linof query came close but did not show members in table however this is how i solved the problem:

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]

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269913

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;

Upvotes: 1

Related Questions