PIVOT and UNPIVOT

PIVOT and UNPIVOT IN SQL

You can use the PIVOT and UNPIVOT relational operators to change 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 they are required on any remaining column values that are wanted in the final output.
UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

The following is annotated syntax for PIVOT.
SELECT <non-pivoted column>,
    [first pivoted column] AS <column name>,
    [second pivoted column] AS <column name>,
    ...
    [last pivoted column] AS <column name>
FROM
    (<SELECT query that produces the data>)
    AS <alias for the source query>
PIVOT
(
    <aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
    IN ( [first pivoted column], [second pivoted column],
    ... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;



Example PIVOT (use Northwind DB)

-- Pivot table with one row and five columns
SELECT 'OrderID' AS OrderID ,
[0], [1], [2], [3], [4]
FROM
(SELECT UnitPrice, Quantity,ProductID
    FROM [Order Details]) AS SourceTable
PIVOT
(
AVG(UnitPrice)
FOR ProductID IN ([0], [1], [2], [3], [4])
) AS PivotTable;

Output
==================
OrderID NULL 18.00 16.4666 NULL NULL
OrderID NULL NULL 19.00 NULL NULL
OrderID NULL NULL NULL NULL NULL
OrderID NULL NULL NULL NULL NULL
OrderID NULL NULL NULL NULL NULL
OrderID NULL 17.10 18.05 9.3333 19.80
OrderID NULL 18.00 NULL NULL NULL
OrderID NULL NULL NULL NULL NULL
OrderID NULL NULL NULL 10.00 NULL
OrderID NULL 18.00 19.00 NULL NULL

###################################################
Example UNPIVOT(use Northwind DB)
;WITH Table_DEMO AS
(
  SELECT
    [FirstName],
    [PostalCode],
    cast(LastName as nvarchar(10)) LastName,
cast(Title as nvarchar(10)) Title
  FROM Table_DEMO
)
SELECT [Key],Value
FROM DDD
UNPIVOT    
(Value FOR [Key] IN        
([FirstName],[PostalCode], [LastName],[Title])) AS unpvt

Output
================
KEY Value
FirstName Nancy
PostalCode 98122
LastName Davolio
Title Sales Repr
FirstName Andrew
PostalCode 98401
LastName Fuller
Title Vice Presi
FirstName Janet
PostalCode 98033
LastName Leverling
Title Sales Repr

#####################################
When applying the UNPIVOT function to data that is not normalized, SQL Server requires that the datatype and length be the same. I understand why the datatype must be the same but why does UNPIVOT require the length to be the same?

Let's say that I have the following sample data that I need to unpivot:

CREATE TABLE People
(
    PersonId int,
    Firstname varchar(50),
    Lastname varchar(25)
)

INSERT INTO People VALUES (1, 'Jim', 'Smith');
INSERT INTO People VALUES (2, 'Jane', 'Jones');
INSERT INTO People VALUES (3, 'Bob', 'Unicorn');
If I attempt to UNPIVOT the Firstname and Lastname columns similar to:

select PersonId, ColumnName, Value
from People
unpivot
(
  Value
  FOR ColumnName in (FirstName, LastName)
) unpiv;
SQL Server generates the error:

Msg 8167, Level 16, State 1, Line 6

The type of column "Lastname" conflicts with the type of other columns specified in the UNPIVOT list.
In order to resolve the error, we must use a subquery to first cast the Lastname column to have the same length as Firstname:

select PersonId, ColumnName, Value
from
(
  select personid,
    firstname,
    cast(lastname as varchar(50)) lastname
  from People
) d
unpivot
(
  Value FOR
  ColumnName in (FirstName, LastName)
) unpiv;

Prior to UNPIVOT being introduced in SQL Server 2005, I would use a SELECT with UNION ALL to unpivot the firstname/lastname columns and the query would run without the need to convert the columns to the same length:

select personid, 'firstname' ColumnName, firstname value
from People
union all
select personid, 'LastName', LastName
from People;


We are also able to successfully unpivot the data using CROSS APPLY without having the same length on the datatype:

select PersonId, columnname, value
from People
cross apply
(
    select 'firstname', firstname union all
    select 'lastname', lastname
) c (columnname, value);