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
('ben@test.com', '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;