Sql-server – SQL Server Dynamic Group By

dynamic-sqlgroup bysql server

I want to create a de-dupe (remove duplicates) records (actually Guest Profiles) from a table (TableA).
The user can opt for 5 criteria (Surname, Name, Email, PostCode,Phone).
This means it can create combinations or use all or leave some out etc

I am trying to create a Dynamic SQL (with sp_executesql to avoid injection) but the problem is that I do not know how many criteria will the user use in order for me to build a GROUP BY clause.

Name    Initial     Email   PostCode    Phone
Guest1  George      g@u.com 33333       777777
Guest1  George      l@i.com 22222       888888
Guest1  George      g@u.com 11111       222222
Guest2  George      g@u.com 22222       000000

Best Answer

You should be able to re-use the same variable that lets you pick your columns, like this:

    create procedure dynamic_group_by (@params nvarchar(max))

    as

    declare @sql nvarchar(max)

    set @sql = 'select ' + @params + ' from profiles p 
    where email like ''%@%'' 
    group by ' + @params

    print sql

    exec sp_executesql @sql
Related Question