如何将列标题转换为贷款编号的行

2021-09-14 00:00:00 sql sql-server unpivot

我陷入了逆向旋转.我在下面有一个像#temp 这样的表.使用 sql server 2008 r2

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

从 #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

推荐答案

您应该能够通过 UNPIVOT 函数使用以下内容:

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;

请参阅SQL Fiddle with Demo.

或者因为您使用的是 SQL Server 2008 R2，这也可以使用 CROSS APPLY 编写:

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);

参见SQL Fiddle with Demo

相关文章