SQL Server Dynamic Group By

Question

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      [email protected] 33333       777777
Guest1  George      [email protected] 22222       888888
Guest1  George      [email protected] 11111       222222
Guest2  George      [email protected] 22222       000000

Answer 1

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