• Is your grouping sets example not just a rework of rollup?

    Is the power of this not in the way it can handle non hierarchical (ie group by col1, col2, col3 with rollup = hierarchical) sets?

    eg

    create table #people(fname sysname, lname sysname, location sysname)

    insert #people

    select 'rich', 'b', 'here'

    union all

    select 'bob', 'b', 'here'

    union all

    select 'rich', 'm', 'there'

    union all

    select 'rich', 'c', 'there'

    union all

    select 'jane', 'c', 'here'

    select

    coalesce(lname, fname, location, 'Total'),

    fname,

    lname,

    location,

    count(*)

    from #people

    group by grouping sets((fname), (lname, location), location, lname, ())

    order by 1