Microsoft SQL Server - Recursive Join

Question

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

Can you give more information I dont understand where the 12 tables came from. — Juan
– Juan, Commented Nov 22, 2013 at 1:25

Michael · Accepted Answer · 2013-11-22 01:36:41Z

1

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.

answered Nov 22, 2013 at 1:36

Michael

1,02818 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Microsoft SQL Server - Recursive Join

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related