Binaryworld - How to transpose columns as rows? This script also demonstrates how to ... [ SQL Server -> T-SQL ]

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)