SQL Statement using Case

Posted on 2005-05-16
Last Modified: 2010-03-19
I have a stored procedure that returns 12 rows each time I run it.  The database was designed that it is storing a record for each Month in rows for example:

Table:  MonthAmounts
MonthID,    MonthAmt
1               5.00
2               10.00
3               17.50
4                 3.50

I want to get everything in one row if possible.  I will always be pulling 12 rows.  Do I have to write a cursor to handle this or can I get it all into 1 record across for all months?  Thanks
Question by:sbornstein2
    1 Comment
    LVL 28

    Accepted Solution

    Try this:

    SELECT SUM(CASE WHEN MonthID = 1 THEN MonthAmt ELSE 0 END) AS Month1,
                SUM(CASE WHEN MonthID = 2 THEN MonthAmt ELSE 0 END) AS Month2,
                SUM(CASE WHEN MonthID = 3 THEN MonthAmt ELSE 0 END) AS Month3,
                SUM(CASE WHEN MonthID = 4 THEN MonthAmt ELSE 0 END) AS Month4,
                SUM(CASE WHEN MonthID = 5 THEN MonthAmt ELSE 0 END) AS Month5,
                SUM(CASE WHEN MonthID = 6 THEN MonthAmt ELSE 0 END) AS Month6,
                SUM(CASE WHEN MonthID = 7 THEN MonthAmt ELSE 0 END) AS Month7,
                SUM(CASE WHEN MonthID = 8 THEN MonthAmt ELSE 0 END) AS Month8,
                SUM(CASE WHEN MonthID = 9 THEN MonthAmt ELSE 0 END) AS Month9,
                SUM(CASE WHEN MonthID = 10 THEN MonthAmt ELSE 0 END) AS Month10,
                SUM(CASE WHEN MonthID = 11 THEN MonthAmt ELSE 0 END) AS Month11,
                SUM(CASE WHEN MonthID = 12 THEN MonthAmt ELSE 0 END) AS Month12,
    FROM MonthAmounts

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now