如何在数据透视中使用多列? [英] How to use multiple columns in pivot?

问题描述

我有这个查询

SELECT [b].[BillID],
       [b].[BillingCode],
       [bc].[CurrentUsage],
       [bc].[Rate],
       [bc].[CurrentCost],
       [c].[Title]
 INTO   #Temp
 FROM   [dbo].[Bills] AS [b]
 INNER JOIN [dbo].[BillCosts] AS [bc] ON [bc].[BillIDRef] = [b].[BillID]
 INNER JOIN [Base].[Costs] AS [c] ON [c].[CostID] = [bc].[CostIDRef]

这是结果

BillID      BillingCode          CurrentUsage Rate        CurrentCost Title
----------- -------------------- ------------ ----------- ----------- ------
5           44545455             10           20          30          AvgTimes
5           44545455             40           50          60          MaxTimes

我需要这个结果:

BillID      BillingCode          AvgTimes Cost  MaxTimes Cost  AvgTimes Rate  MaxTimes Rate AvgTimes Usage MaxTimes Usage
----------- -------------------- -------------- -------------- -------------- ------------- -------------- ---------------
5           44545455             30             60             20             50            10             40

是否可以在多列 CurrentUsage,Cost,Rate 上使用数据透视表?如果无法使用数据透视表，如何编写查询?

Is it possible using pivot on multiple columns CurrentUsage,Cost,Rate? If isn't possible using pivot, How to write query?

推荐答案

你可以使用条件聚合:

SELECT [b].[BillID],
       [b].[BillingCode],
       MAX(CASE WHEN [c].[Title] = 'AvgTimes' THEN [bc].[CurrentUsage] END) AS [AvgTimes Usage],
       MAX(CASE WHEN [c].[Title] = 'MaxTimes' THEN [bc].[CurrentUsage] END) AS [MaxTimes Rate],
       MAX(CASE WHEN [bc].[Title] = 'AvgTimes' THEN [bc].[Rate] END) AS [AvgTimes Rate],
       MAX(CASE WHEN [bc].[Title] = 'MaxTimes' THEN [bc].[Rate] END) AS [MaxTimes Usage],
       MAX(CASE WHEN [bc].[Title] = 'AvgTimes' THEN [bc].[CurrentCost] END) AS [AvgTimes CurrentCost],
       MAX(CASE WHEN [bc].[Title] = 'MaxTimes' THEN [bc].[CurrentCost] END) AS [MaxTimes CurrentCost]
INTO   #Temp
FROM   [dbo].[Bills] AS [b]
INNER JOIN [dbo].[BillCosts] AS [bc] ON [bc].[BillIDRef] = [b].[BillID]
INNER JOIN [Base].[Costs] AS [c] ON [c].[CostID] = [bc].[CostIDRef]
GROUP BY [b].[BillID], [b].[BillingCode]

这篇关于如何在数据透视中使用多列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！