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.
Group By Expressions
Seth Griffin
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
The Happy Friar
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
silver6
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.
Zoya Bashirova
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
StaC
Erik Sharp
.Net Junkie
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...