sql - 动态旋转表

Question

我有一个类似于下面的表结构:

CREATE TABLE #temp
(
    period_id INTEGER ,
    lease_id  INTEGER  ,
    charge_id VARCHAR(20) ,
    charge_amount MONEY
)

INSERT INTO #temp
        ( period_id ,
          lease_id ,
          charge_id ,
          charge_amount
        )
VALUES  ( 100 , -- period_id - integer
          2000 , -- lease_id - integer
          '300' , -- charge_id - varchar(20)
          12345  -- charge_amount - money
        ) ,
        ( 101 , -- period_id - integer
          2000 , -- lease_id - integer
          '300' , -- charge_id - varchar(20)
          678910  -- charge_amount - money
        ) ,
        ( 101 , -- period_id - integer
          2002 , -- lease_id - integer
          '300' , -- charge_id - varchar(20)
          78950  -- charge_amount - money
        ) ,
        ( 101 , -- period_id - integer
          2002 , -- lease_id - integer
          '310' , -- charge_id - varchar(20)
          9002  -- charge_amount - money
        )

我想要一个如下所示的输出:

我相信我可以通过创建一系列 cte 并不断地返回源表来实现这一点，但是我认为这不是一个很好的解决方案，因为我有很多这样的收费代码，并且预计会有新的将来添加(并且每次发生这种情况时都必须更新查询将是糟糕的做法)。我已经研究过 pivot 函数，但我不明白如何使用它，我想了解如何在这种情况下应用它，以便我可以将它添加到我的武器库中。

Answer 1

试试这个:

Select period_id,lease_id,[300] as charge_300, [310] as Charge_310
FROM
(Select period_id,lease_id,charge_id,charge_amount 
 from #temp )p
pivot
(
 sum(charge_amount)
 FOR charge_id in ([300],[310])
) as PVT

SQLFIDDLE演示

如果您需要列数不固定的动态 PIVOT，请尝试以下查询

DECLARE @columns VARCHAR(8000)

SELECT 
@columns = 
COALESCE
(
 @columns + ',[' + charge_id + ']',
 '[' + charge_id + ']'
)
FROM 
#temp 
group by charge_id

// The above query tries to create distinct charge_id as the columns
// @columns= [300],[301]

DECLARE @query VARCHAR(8000)
SET @query = 'SELECT *
FROM
(
 SELECT 
 period_id,lease_id ,charge_id,charge_amount 
 FROM 
 #temp 
) P
PIVOT
(
 SUM(charge_amount) 
 FOR charge_id in (' + @columns + ')
) AS PVT'

 EXECUTE (@query)
 GO