T-SQL Dynamic GROUP BY using @parameters

I would like to achieve

SELECT @param1, @param2, @param3, t.field1, sum(t.amount)
FROM table t
WHERE t.field 2 IS NOT NULL AND
t.field3ID = '12345'
GROUP BY @param1, @param2, @param3

What is the best approach? Is building dynamic SQL is the way to go? 开发者_StackOverflow

Dynamic SQL is the only way to go here. However, what kind of table do you have where you have a bunch of optional grouping columns?

First of all t.field1 should also be in your group by or handled in the sql with an aggrigate function like min or max

Here is a bit of dynamic sql you can use. It will allow you to use different number of parameters

DECLARE @t TABLE (COLUMNNAME varchar(15))
DECLARE @pstring VARCHAR(1000), @sqlstring varchar(5000)

DECLARE @param1 VARCHAR(15)
DECLARE @param2 VARCHAR(15)
DECLARE @param3 VARCHAR(15)
--declare more columns here

SET @param1 = <colname> -- replace this <colname>
SET @param2 = <colname>
SET @param3 = <colname>
--set the name of the column

INSERT @t SELECT @param1 
UNION ALL SELECT @param2
UNION ALL SELECT @param3
--union all select @param4 etc

SELECT @pstring = COALESCE(@pstring, '') + columnname+',' FROM @t

SET @sqlstring = 'SELECT '+@pstring + 'min(t.field1) field1, sum(t.amount)
FROM table t
WHERE t.field2 IS NOT NULL AND
t.field3ID = ''12345''
GROUP BY ' +stuff(@pstring,len(@pstring), 1,'')

EXEC(@sqlstring)

Why would you need to group by values you are explicitly passing into your query?

Eliminate the @param1/2/3 from the select statement, eliminate the group by and just do a regular select. In your code, add your parameters to whatever is expecting the results. Otherwise you're just creating network traffic.

I'm boldly assuming that your params hold your field names. Otherwise I see no reason why you would have them there.

If the grouped items contains exactly the same values within the groups, you could do without grouping by using min or max like this:

SELECT Min(@param1), Min(@param2), Min(@param3), t.field1, sum(t.amount)
FROM table t
WHERE t.field 2 IS NOT NULL AND
t.field3ID = '12345'

You would still have the problem that fields can't be selected like that.

You could use case statements, this works and can be combined with the "Min/Max" philosophy from above if you want to get rid of the group by statements:

SELECT 
    case 
        when @param1 = 'colname1' then colname1
        when @param1 = 'colname2' then colname2
        when @param1 = 'colname2' then colname3
        else null
    end, 
    case 
        when @param2 = 'colname1' then colname1
        when @param2 = 'colname2' then colname2
        when @param2 = 'colname2' then colname3
        else null
    end, 
    case 
        when @param3 = 'colname1' then colname1
        when @param3 = 'colname2' then colname2
        when @param3 = 'colname2' then colname3
        else null
    end, 
    t.field1, 
    sum(t.amount)
FROM table t
WHERE t.field2 IS NOT NULL AND
t.field3ID = '12345'
GROUP BY 
    case 
        when @param1 = 'colname1' then colname1
        when @param1 = 'colname2' then colname2
        when @param1 = 'colname2' then colname3
        else null
    end, 
    case 
        when @param2 = 'colname1' then colname1
        when @param2 = 'colname2' then colname2
        when @param2 = 'colname2' then colname3
        else null
    end, 
    case 
        when @param3 = 'colname1' then colname1
        when @param3 = 'colname2' then colname2
        when @param3 = 'colname2' then colname3
        else null
    end,
    t.field1

I think you could choose between the following solutions:

• Select all required fields and filter fields at the client side
• Use dynamic SQL
• Create multiple procedures
• Use case statements

It all depends on how you are going to use it.