다음을 통해 공유

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:

 

image

but we want it to appear like this:

image

 

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;

Comments

  • Anonymous
    June 07, 2012
    Thanks for sharing
  • Anonymous
    September 05, 2013
    The comment has been removed
  • Anonymous
    September 01, 2014
    Hi, Thanks for sharing,  very useful for my current requirement. I was wondering if there is a way incorporate another attribute, Value For Property In with different values as in having a second attribute in the unpivot condition? Note: Inner joining tblpivot with another table. Appreciate your help