Repeatedly JOIN data horizontally

Question

I have two tables that look like this:

Categories ( CategoryId, Type, Name )
Transactions ( TxnId, DateTime, Amount, CategoryId )

I want to get the SUM of all of the transactions for each category for each month.

I can do this by having a sproc (see below) that generates the sums for each category given a date-range (e.g. a month 2013-07-01 <= Transactions.DateTime < 2013-08-01. However I don't know how to do that for all months in the database, so I decided I could run the sproc repeatedly (given different min/max dates) to get the sums for different months.

I would like to combine the results into a single table.

My category sum sproc looks like this:

SELECT
    Categories.Name,
    Categories.Type,
    Categories.CategoryId,
    CategorySpendingForMonth.Amount
FROM
    Categories
    LEFT OUTER JOIN (

        SELECT
            CategoryId,
            SUM(Amount) AS Amount
        FROM
            Txns
        WHERE
            [DateTime] >= @fromDate
            AND
            [DateTime] < @toDate
        GROUP BY
            CategoryId

    ) AS CategorySpendingForMonth ON Categories.CategoryId = CategorySpendingForMonth.CategoryId
ORDER BY
    Categories.Type,
    Categories.Name

My result looks like this:

Name        Type    CatId    Amount
Car         0       9       -183.22
Electricity 0       12      -86.05
Insurance   0       17      NULL
Internet    0       14      -39.99
Phone       0       23      -50.04
Rent        0       19      -2284.80
Xoom        0       25      -604.99

But I want it to look like this:

Name        Type    CatId    June       July        Aug
Car         0       9       -183.22     -183.22     -183.22
Electricity 0       12      -86.05      -86.05      -86.05
Insurance   0       17      NULL        -12.30      NULL
Internet    0       14      -39.99      -39.99      -39.99
Phone       0       23      -50.04          -50.04  -50.04
Rent        0       19      -2284.80    -2284.80    -2284.80
Xoom        0       25      -604.99     -604.99     -604.99

(and add additional columns for additional months)

So I was thinking I could simply call my sproc for each month I want to add the data for, but I don't know how to JOIN my existing data together like that (if it were vertical I'd use a UNION, but this is horizontal).

This is what I have in mind (invalid SQL):

SELECT 
    Name,
    Type,
    CategoryId,
    Amount
FROM
    EXEC CategoriesSummary( '2013-07-01', '2013-08-01' )
    FOR i = 0 TO 12
        LEFT OUTER JOIN CategoriesSummary( '2013-' + (7 + i) +'-01', '2013-' + (8 + 1) + '-01' )
    NEXT

....but that's invalid, so what's the best way to solve this problem?

Thanks!

score 1 · Answer 1 · edited May 23 '17 at 12:13

For more general solution, take a look at my answer in other question https://stackoverflow.com/a/13583258/1744834.

For less general solution :), you could use PIVOT for that:

declare @Year int = 2013

;with CTE as
(
    select C.Name, datepart(mm, T.DateTime) as M, sum(T.Amount) as Amount
    from Txns as T
        left outer join Categories as C on C.categoryId = T.categoryID
    where
        T.DateTime >= dateadd(yy, 2013 - datepart(yy, 0), 0) and
        T.DateTime < dateadd(yy, 2013 - datepart(yy, 0) + 1, 0)
    group by C.Name, datepart(mm, T.DateTime)
)
select
    Name,
    p.[1] as [January],
    p.[2] as [February],
    p.[3] as [March],
    p.[4] as [April],
    p.[5] as [May],
    p.[6] as [June],
    p.[7] as [July],
    p.[8] as [August],
    p.[9] as [September],
    p.[10] as [October],
    p.[11] as [November],
    p.[12] as [December]
from CTE as C
pivot 
(
    sum(Amount)
    for M in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) as p

see SQL FIDDLE EXAMPLE

score 0 · Answer 2 · answered Jul 28 '13 at 04:10

You'd probably be better off using a table-valued function in this case.

I can't verify the syntax, but something like this:

CREATE FUNCTION dbo.tvf(@fromDate DATETIME)
RETURNS @rows TABLE 
(
    val UNIQUEIDENTIFIER PRIMARY KEY
)
AS 
BEGIN
INSERT INTO @rows
SELECT
    valId AS val
FROM
    Values
WHERE changed > @fromDate
ORDER BY
    valid
RETURN
END

You can then use your TVF like:

SELECT * FROM dbo.tvf('1/1/2000')

The documentation for TVFs is here: http://msdn.microsoft.com/en-us/library/ms191165(v=SQL.105).aspx

Repeatedly JOIN data horizontally

2 Answers2