如何在SQL中正确创建动态枢轴函数?

Question

我带着问题回来了。我一直在尝试创建一个动态枢轴过程，但我在创建列时遇到了一些麻烦。

的例子:

tbody> <<tr>03号

TranID	银行名称	NetAmount	账户
01	StormWindBank	23.0 $
02	StormWindBank	14.0 $	B
	StormWindBank	00.0 $
04	StormWindBank	12.0 $	B

您需要使用row_number()

从派生列中PIVOT

Declare @SQL varchar(max) = stuff( ( Select Distinct concat(',[NetAmount',row_number() over (partition by [Bank Name],[Account] order by [TranID]),']' ) 
From #TEMP For XML Path('') ),1,1,'')
Set @SQL = '
Select *
From (
Select [Account]
,[Bank Name]
,Item  = concat(''NetAmount'',row_number() over (partition by [Bank Name],[Account] order by [TranID]) )
,Value = [NetAmount]
from #TEMP
) src
Pivot ( max( [Value] ) for Item in ('+ @SQL   +') ) pvt
'
Exec(@SQL)

结果

Account Bank Name       NetAmount1  NetAmount2
A       StormWindBank   23.0$       00.0$
B       StormWindBank   14.0$       12.0$