CODEWITHSUNDEEP
sqlsql-serverjoinrecursion
user3019879
user3019879

Reputation: 11

Microsoft SQL Server - Recursive Join

Using Microsoft SQL Server 2008 R2, I am trying to recursively join monthly data from 12 queries into 1 query and results table. I know how to join the results if I write out the query 12 times and join each one but the query is 16 lines long already, so I am looking for a way to do it recursively.

I will use a sales example:
I have a table with the following defintion 

sales(ID int, EmpID int, dt datetime, amount numeric(5,2)).

With the following query, I get 12 tables, how do I join them all together on EmpID and how can I name the column header with the month/year:

DECLARE @mo INT, @yr INT
SET @mo = 1
SET @yr = YEAR(GETDATE())
WHILE (@mo < 13)
BEGIN
  SELECT EmpID, SUM(numeric) AS Totals WHERE MONTH(dt) = @mo AND YEAR(dt) = @yr GROUP BY EmpID
  SET @mo = @mo + 1
END

I would like the results to be something like:

EmpID      1-2012     2-2012    ...    12-2012
-----     -------    -------           -------
1         XXXX.XX    XXXX.XX           XXXX.XX
...
100       XXXX.XX    XXXX.XX           XXXX.XX

Upvotes: 1

Views: 229

Answers (1)

Michael
Michael

Reputation: 1028

You could read about recursive common table expressions. EG:

;WITH Months AS
(
    SELECT 1 as [Month]

    UNION ALL

    SELECT [Month] + 1
    FROM Months
    WHERE [Month] + 1 < 13
), 
SELECT [Month]
FROM Months

But I think you're looking for the pivot operator. EG:

;WITH SalesByMonth AS (
    SELECT
      EmpID
     ,MONTH(dt) AS Month
     ,SUM(Amount) AS Total
    FROM
      @sales
    WHERE
      YEAR(dt) = @Year
    GROUP BY
      EmpID
     ,MONTH(dt)
)
SELECT 
  EmpID, [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
FROM (
  SELECT 
    EmpID
   ,Month
   ,Total
  FROM 
    SalesByMonth) up
PIVOT (SUM(Total) FOR Month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS pvt
ORDER BY 
  EmpID

Check out the SQLFiddle example here.

Upvotes: 1

Related Questions