Post reply

Multiple Select Headers in a SP

  • March 27, 2014 at 1:12 pm

    #283944

    Hi Guys,

    I want to display headers on the following SP, but only the first column header appears. How can I split them.

    ALTER PROCEDURE CountCases

    AS

    SET NOCOUNT ON;

    SELECT ISNULL(SUM(Civil_Case_Files_Page_Count),0) as [Total Civil Cases] FROM dbo.DSObject_table WHERE handle_class=21

    UNION ALL

    SELECT ISNULL(SUM(Criminal_Case_Files_Page_Count),0)as [Total Criminal Cases] FROM dbo.DSObject_table where handle_class=22

    UNION ALL

    SELECT ISNULL(SUM(Admiralty_Case_File_Page_Count),0) as [Total Admiralty Cases] FROM dbo.DSObject_table WHERE handle_class=23

    GO

    Thanks.

  • March 27, 2014 at 1:26 pm

    #1700865

    Each column of a resultset will have one header. You could remove the UNION ALLs to have 3 resultsets, add a column for the description of the row or have 3 columns on your resultset.

    Here's the third option:

    SELECT ISNULL(SUM(CASE WHEN handle_class = 21 THEN Civil_Case_Files_Page_Count END),0) as [Total Civil Cases],

    ISNULL(SUM(CASE WHEN handle_class = 22 THEN Criminal_Case_Files_Page_Count END),0) as [Total Criminal Cases],

    ISNULL(SUM(CASE WHEN handle_class = 23 THEN Admiralty_Case_File_Page_Count END),0) as [Total Admiralty Cases]

    FROM dbo.DSObject_table

    WHERE handle_class IN(21,22,23)

    The first option is evident and for the second one, I'm afraid that I can't give an exact result without having some DDL and sample data. To know how to post this, read the article linked in my signature.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply