t-sql pivot - monthly revenue

pivot, sql-server, t-sql

Solution

Test Data

CREATE TABLE #MyTable ([MONTH] NVARCHAR(20),RevenueGroup NVARCHAR(100),AMOUNT NUMERIC(20,2))
GO
INSERT INTO #MyTable
VALUES
('December','Dues - Collect',       425725.71),
('December','NMS',                  78444.17),
('December','Personal Training',    58511.53),
('November','Dues - Collect',       425683.75),
('November','NMS',                  114710.25),
('November','Personal Training',    66277.49),
('October','Dues - Collect',       419250.09),
('October','NMS',                  96098.86),
('October','Personal Training',    58725.77)

Query

SELECT RevenueGroup, [October],[November],[December]
FROM (SELECT * FROM #MyTable)t   --<-- Your Existing Query can go in here but do not
  PIVOT                             -- not do any formating like Dollar sign as you want
      (                             -- sum the results by month later on    
      SUM(AMOUNT)
      FOR [MONTH]
      IN ([October],[November],[December])
      )P

Result

╔═══════════════════╦═══════════╦═══════════╦═══════════╗
║   RevenueGroup    ║  October  ║ November  ║ December  ║
╠═══════════════════╬═══════════╬═══════════╬═══════════╣
║ Dues - Collect    ║ 419250.09 ║ 425683.75 ║ 425725.71 ║
║ NMS               ║ 96098.86  ║ 114710.25 ║ 78444.17  ║
║ Personal Training ║ 58725.77  ║ 66277.49  ║ 58511.53  ║
╚═══════════════════╩═══════════╩═══════════╩═══════════╝

Problem

I'm having trouble figuring out how to construct the `PIVOT` for my revenue query. First, here's my query: ``` SELECT DATENAME(month, CONVERT(DATETIME, CONVERT(VARCHAR, TransactionMonth) + '/1' + '/2013')) AS MONTH , RevenueGroup , AMOUNT FROM ( SELECT f.TransactionMonth , f.RevenueGroupID , '$' + CONVERT(VARCHAR, CAST(SUM(f.LineItemAmount) AS MONEY), -1) AS Revenue , MIN(f.RevenueGroup) AS RevenueGroup FROM Financials(@StartDate, @EndDate, @SiteID) f GROUP BY f.TransactionMonth , f.RevenueGroupID ) AS Rev ORDER BY Rev.TransactionMonth DESC , CAST(Revenue AS MONEY) DESC ``` And here is my result set: ``` MONTH RevenueGroup AMOUNT December Dues - Collect $425,725.71 December NMS $78,444.17 December Personal Training $58,511.53 November Dues - Collect $425,683.75 November NMS $114,710.25 November Personal Training $66,277.49 October Dues - Collect $419,250.09 October NMS $96,098.86 October Personal Training $58,725.77 ``` And here is how the result set needs to look after the sql PIVOT: ``` RevenueGroup DECEMBER NOVEMBER OCTOBER Dues - Collect $425,725.71 $425,683.75 $419,250.09 NMS $78,444.17 $114,710.25 $96,098.86 Personal Training $58,511.53 $66,277.49 $58,725.77 ``` How can I achieve this with T-SQL PIVOT?

Original source