How to use multiple columns in pivot?

Question

I have this query

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]

this is result

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

I need this result:

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

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

Answer 1

You can use conditional aggregation:

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]