How to convert Column header to Row for loannumber

Question

I am stuck in unpivoting. I have a table like #temp below. Using sql server 2008 r2

Select LoanNumber = 2000424385
    ,[AmntType1] = 120.32
    ,[AmntType2] = 131.52
    ,[AmntType3] = 142.36
    into #temp

select * from #temp

Above table has only one row and i want three rows as below

LoanNumber Amount AmountType
2000424385 120.32 AmntType1
2000424385 131.52 AmntType2
2000424385 120.32 AmntType1

Answer 1

You should be able to use the following with the UNPIVOT function:

select loanNumber,
  amount, 
  amounttype
from #temp
unpivot
(
  amount
  for amounttype in (AmntType1, AmntType2, AmntType3)
) unp;

See SQL Fiddle with Demo.

Or because you are using SQL Server 2008 R2, this can also be written using CROSS APPLY:

select loannumber, 
  amount,
  amounttype
from #temp
cross apply
(
  values
    ('AmntType1', AmntType1),
    ('AmntType2', AmntType2),
    ('AmntType3', AmntType3)
) c (amounttype, amount);

See SQL Fiddle with Demo