create table #Person (personID int, FirstName varchar(20), LastName varchar(20),
Phone varchar(12), Age int)

insert #Person values(1, 'first1', 'last1', 'phone1', 1)
insert #Person values(2, 'first2', 'last2', 'phone2', 2)

Select case when 
        grouping(personid) = 0 And Grouping(firstname) = 1 And 
        Grouping(lastname) = 1 And grouping(phone) = 1 And
        Grouping(age) = 1
                then 'PersonID'
        when 
        grouping(personid) = 1 And Grouping(firstname) = 0 And 
        Grouping(lastname) = 1 And grouping(phone) = 1 And
        Grouping(age) = 1
                then 'FirstName'
        when 
        grouping(personid) = 1 And Grouping(firstname) = 1 And 
        Grouping(lastname) = 0 And grouping(phone) = 1 And
        Grouping(age) = 1
                then 'LastName'
        when 
        grouping(personid) = 1 And Grouping(firstname) = 1 And 
        Grouping(lastname) = 1 And grouping(phone) = 0 And
        Grouping(age) = 1
                then 'Phone'
        when 
        grouping(personid) = 1 And Grouping(firstname) = 1 And 
        Grouping(lastname) = 1 And grouping(phone) = 1 And
        Grouping(age) = 0
                then 'Age'
        end as FieldName,
        case when 
        grouping(personid) = 0 And Grouping(firstname) = 1 And 
        Grouping(lastname) = 1 And grouping(phone) = 1 And
        Grouping(age) = 1
                then ltrim(str(personid))
        when 
        grouping(personid) = 1 And Grouping(firstname) = 0 And 
        Grouping(lastname) = 1 And grouping(phone) = 1 And
        Grouping(age) = 1
                then firstname
        when 
        grouping(personid) = 1 And Grouping(firstname) = 1 And 
        Grouping(lastname) = 0 And grouping(phone) = 1 And
        Grouping(age) = 1
                then LastName
        when 
        grouping(personid) = 1 And Grouping(firstname) = 1 And 
        Grouping(lastname) = 1 And grouping(phone) = 0 And
        Grouping(age) = 1
                then Phone
        when 
        grouping(personid) = 1 And Grouping(firstname) = 1 And 
        Grouping(lastname) = 1 And grouping(phone) = 1 And
        Grouping(age) = 0
                then Ltrim(Str(Age))
        end as FieldValue
from #Person 
group by personid, firstname, lastname, phone, age
with cube
having (grouping(personid) = 0 And Grouping(firstname) = 1 And 
        Grouping(lastname) = 1 And grouping(phone) = 1 And
        Grouping(age) = 1)
        or
        (grouping(personid) = 1 And Grouping(firstname) = 0 And 
        Grouping(lastname) = 1 And grouping(phone) = 1 And
        Grouping(age) = 1)
        or
        (grouping(personid) = 1 And Grouping(firstname) = 1 And 
        Grouping(lastname) = 0 And grouping(phone) = 1 And
        Grouping(age) = 1)
        or
        (grouping(personid) = 1 And Grouping(firstname) = 1 And 
        Grouping(lastname) = 1 And grouping(phone) = 0 And
        Grouping(age) = 1)
        or
        (grouping(personid) = 1 And Grouping(firstname) = 1 And 
        Grouping(lastname) = 1 And grouping(phone) = 1 And
        Grouping(age) = 0)

This page was last updated on May 01, 2006 04:28 PM.