SQL query to return multiple SUMs in a single line result

Another good question from one of my customers today --

"... how to take two or more records and summarize them into one record. "  

I can't tell you how many times I've done this when reporting traded volumes.  Daily, weekly or monthly, grouped by symbol, or trader, or even trading desk.... This new customer's data is a little different, but the approach is the same.  This is just a quick post, providing a method to bring your data counts back in single line result-sets.

USE DBA; -- change to your dbname

DECLARE @input TABLE (UserID INT, FiscalYear INT, GiftAmt DECIMAL(5,2))

INSERT @input (UserID,FiscalYear,GiftAmt)

VALUES (1,2012,100.00),

  (1,2012,50.00),

  (1,2013,110.00),

  (1,2014,50.00),

  (1,2014,50.00),

  (1,2014,75.00),

  (2,2012,65.00),

  (2,2014,55.00),

  (2,2014,55.00),

  (2,2014,75.00),

  (3,2012,10.00),

  (5,2011,20.00),

  (5,2013,55.00),

  (5,2014,55.00);

-- Look at your data, see you've got 14 records

-- SELECT * FROM @input

-- Now bring it back out in 4 lines, one for each UserID

SELECT 

UserID,

   ISNULL(SUM(FY1Amt),0.00) [FY1Amt],

   ISNULL(SUM(FY2Amt),0.00) [FY2Amt],

   ISNULL(SUM(FY3Amt),0.00) [FY3Amt],

   ISNULL(SUM(FY4Amt),0.00) [FY4Amt]

FROM

(

SELECT 

UserID,

FiscalYear,

CASE WHEN FiscalYear = 2011 THEN SUM(GiftAmt) END [FY1Amt],

CASE WHEN FiscalYear = 2012 THEN SUM(GiftAmt) END [FY2Amt],

CASE WHEN FiscalYear = 2013 THEN SUM(GiftAmt) END [FY3Amt],

CASE WHEN FiscalYear = 2014 THEN SUM(GiftAmt) END [FY4Amt]

FROM

@input a

GROUP BY

UserID,

FiscalYear 

) x

GROUP BY 

x.UserID

Your results will look like this:

   UserID   FY1Amt  FY2Amt  FY3Amt    FY4Amt

      1          0.00       150.00     110.00       175.00

      2          0.00         65.00       0.00       185.00

      3          0.00        10.00        0.00          0.00

      5        20.00          0.00      55.00         55.00

Now that I think about it, I believe I did a similar post once before, for the single-line result sets. A different method, maybe a bit older.. but the single line output is achieved the same.  If you haven't seen that one already, take a look at it here.

I will warn as I did on that first post.  SQL Server is used to store and manage the data, not to make it pretty. There are many very clever things you can do to manipulate your output, but you've got to be wary of the performance implications. Here's a little information from MSFT on the same thing.  See the the 'Logic separation' section, about keeping the business logic separate from the data manipulation.