Group By Expressions

I'd like to dynamically create a group by expression. I have

select sourcecd
FROM dbo.ITMV_ScanCardHistory
GROUP BY dbo.fn_GroupBy (@.GroupBy)

Where:

@.groupby = sourcecd and

fn_GroupBy =

CREATE FUNCTION dbo.fn_GroupBy
(@.ColumnName Varchar(55))
RETURNS Varchar(55)
AS
BEGIN

RETURN @.ColumnName
END

I keep getting this error message:

Server: Msg 164, Level 15, State 1, Line 48
GROUP BY expressions must refer to column names that appear in the select list.

Please help.

Hello,

This won't work as the Group By clause is looking for a column name, whereas you are supplying a varchar(55) string...

Have you looked at dynamic statements via ExecuteSQL?

Have a look at EXECUTE in BOL.

Cheers

Rob

|||

The error is saying that what you have in your GROUP by you need in your select.

Why do you want to do this, there are a number of options but I would like to understand why you need this first.

|||thank you. We are designing reports in reporting services and we'd like to give the business user the option of selection which column they'd like to group their data by.|||

thanks, however SQL Books says that scalar functions or any valid expression can be used in a group by:

see "

Invoking User-Defined Functions That Return a Scalar Value"

under Accessing and Changing Relation...

|||

You can do it in RS by making the group by field dynamic. In your group define the field to group by as fields(Paramaters("GroupBy").Value).Value

Or something like that

|||Thank you, I will give this a try.|||

Hi,

By using sp_executesql you can create your dynamic sql and run it as below

declare @.sql as nvarchar(1000)
SELECT @.sql = N'
select ' + dbo.fn_GroupBy('eventid') +
N' FROM Events
GROUP BY ' + dbo.fn_GroupBy('eventid')

-- SELECT @.sql

exec sp_executesql @.sql

But you can also use "distinct" instead of "group by"

declare @.sql as nvarchar(1000)
SELECT @.sql = N'
select distinct ' + dbo.fn_GroupBy('eventid') + N' FROM Events '
-- SELECT @.sql

exec sp_executesql @.sql

Eralper

http://www.kodyaz.com