MattB February 2016

SQL Server: Returning Results of Group By Based on Substring of Column

For more context on this question please see here.

I have a query which uses GROUP BY with a substring

    use thedatabase;

    declare @fromDate datetime = '2016-02-01 00:00:00.000';
    declare @toDate datetime = '2016-02-02 23:59:59.999';
    declare @source varchar(15) = 'server001';

    DECLARE @countForType bigint;
    DECLARE @totalForType decimal(30,8);

    DECLARE @country varchar(10);

    SELECT  @countForType = count(*),
            @totalForType = SUM(typeTable.amount),
            @country = 
                case
                when (charindex('[', typeTable.source) > 0 and charindex(']', typeTable.source) > 0)
                then substring(typeTable.source, charindex('[', typeTable.source) +1, (charindex(']', typeTable.source) - 1) - charindex('[', typeTable.source))
                else null
                end
    FROM 
        theTypeTable typeTable (nolock)
    WHERE 
        typeTable.startDate > @fromDate
        AND typeTable.startDate < @toDate
        AND typeTable.source like @source 
    GROUP BY                     
        case
            when (charindex('[', typeTable.source) > 0 and charindex(']', typeTable.source) > 0)
            then substring(typeTable.source, charindex('[', typeTable.source) +1, (charindex(']', typeTable.source) - 1) - charindex('[', typeTable.source))
            else null
        end

I'd like to be able to get this into a report format, either by looping through and print out values with PRINT or place the it all in a result set. Either would be fine.

From researching, there are two things that I could do here to compile a report:

  1. Use a cursor and loop through the results, printing the values. I'm not sure how to go about doing this.

  2. Get rid of the scalar variab

Answers


Naveed February 2016

I would say you can use a bit scripting, like

SELECT  'countForType: ' & count (*) + '
totalForType: ' & SUM(typeTable.amount) as totalForType 
FROM 
....

Here after the last apostrophe in the line, I pressed enter, that will make the line change part of the query.

Execute the query in Text mode, by pressing Ctrl+T first and hope you will get the result.

Post Status

Asked in February 2016
Viewed 1,560 times
Voted 10
Answered 1 times

Search




Leave an answer