Transposing Columns onto Rows
After a long period of absence, I have returned to posting some blog articles. This one popped up last week from an Oracle DBA while I was onsite with a customer. The conversation went something along the lines of:
Oracle DBA: “How can I place columns values on rows? I have a limited page width and need to alter the output”
Microsoftie “ah.. well, that’s easy..”
Consider the output below:
but we want it to appear like this:
The magic is really in the UNPIVOT function as shown below.
CREATE DATABASE sandbox; USE sandbox; CREATE TABLE tblPerson ( Email_Address varchar(50), First_Name varchar(50), Last_Name varchar(50) ); INSERT INTO tblPerson VALUES ('firstname.lastname@example.org', 'Ben', 'WJ') SELECT * FROM tblPerson; SELECT tblPivot.Property, tblPivot.Value FROM (SELECT CONVERT(sql_variant,Email_Address) AS Email_Address, CONVERT(sql_variant,First_Name) AS First_Name, CONVERT(sql_variant,Last_Name) AS Last_Name FROM tblPerson) Person UNPIVOT (Value For Property In (Email_Address, First_Name, Last_Name)) as tblPivot;