Using PIVOT and UNPIVOT

Next Post
By Admin at 23 Dec 2009, 19:13 PM
  • You can use the PIVOT and UNPIVOT relational operators to manipulate a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where necessary on any remaining column values that are desired in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values. Every PIVOT query involves an aggregation of some type.

    There are two ways to pivot data:

    1. We can convert multiple rows into a single row with repeating groups of columns.
    2. We can convert columns on a single row into multiple rows with a generic column and a row type discriminator.


    Use the following scripts,

    1. CREATETABLE Sales (State CHAR(2), SalesAmt DECIMAL(18,2))

    Insert values,

    1. INSERTINTO Sales VALUES ('ND',10000)
    2. INSERTINTO Sales VALUES ('SD',30000)
    3. INSERTINTO Sales VALUES ('TN',2500.50)
    4. INSERTINTO Sales VALUES ('OR',5500.50)
    5. INSERTINTO Sales VALUES ('VA',6500.50)
    6. INSERTINTO Sales VALUES ('SD',7000)
    7. INSERTINTO Sales VALUES ('ND',8000)
    8. SELECT * FROM Sales


    Here is the result set,

    undefined

    Run the following query,

    1. GO
    2. SELECT [ND],[SD],[TN],[OR],[VA]
    3. FROM (SELECT State,SalesAmt FROM Sales) p
    4. PIVOT
    5. (
    6. SUM (SalesAmt)
    7. FOR State IN
    8. ([ND],[SD],[TN],[OR],[VA])
    9. ) AS pvt


    Here is the result set,

    undefined


    UNPIVOT performs almost the reverse operation of PIVOT, by rotating columns into rows.
    Use the following scripts,

    1. Create the following table,
    2. CREATETABLE StudentMarks(
    3. [Name] VARCHAR(50),
    4. Subject1 VARCHAR(10),
    5. Mark1 INT,
    6. Subject2 VARCHAR(10),
    7. Mark2 INT,
    8. Subject3 VARCHAR(10),
    9. Mark3 INT)


    Insert values,

    1. INSERTINTO StudentMarks([Name],Subject1,Mark1,Subject2,Mark2,Subject3,Mark3)
    2. VALUES('AAA','Science',98,'Maths',89,'English',76)
    3. INSERTINTO StudentMarks([Name],Subject1,Mark1,Subject2,Mark2,Subject3,Mark3)
    4. VALUES('XXX','Biology',78,'Chemistry',85,'Physics',67)
    5. INSERTINTO StudentMarks([Name],Subject1,Mark1,Subject2,Mark2)
    6. VALUES('YYY','Batany',60,'Zoology',54)
    7. INSERTINTO StudentMarks([Name],Subject1,Mark1,Subject2,Mark2)
    8. VALUES('ZZZ','Maths',67,'Physics',78)
    9. SELECT * FROM StudentMarks


    Here is the result set,

    undefined
    Run the following query,

    1. SELECT [Name], SubjectName,
    2. casewhen Subject='Subject1'then Mark1
    3. when Subject='Subject2'then Mark2
    4. when Subject='Subject3'then Mark3
    5. elseNULLendas Marks
    6. FROM
    7. (SELECT [Name], Subject1,Mark1, Subject2,Mark2, Subject3,Mark3
    8. FROM StudentMarks) p
    9. UNPIVOT
    10. (SubjectName FOR Subject IN
    11. (Subject1, Subject2, Subject3)
    12. )AS unpvt;

    Here is the result set,

    undefined

    Note that UNPIVOT is not the exact reverse of PIVOT. PIVOT performs an aggregation and hence merges possible multiple rows into a single row in the output. UNPIVOT does not reproduce the original table-valued expression result because rows have been merged. Besides, NULL values in the input of UNPIVOT disappear in the output, whereas there may have been original NULL values in the input before the PIVOT operation.


    Comments

     

    Post a comment

    Please correct the following: