My Technical Notes

Monday, 2 November 2015

SQL PIVOT Statement

Given tabular data:


UserName ColumnName   Value
-------- ------------ --------
hasst001 ComputerName WST0XXXX
hasst001 Forename     Tahir
hasst001 Surname      Hassan

we can turn it into tabular using the following SQL Statement:


WITH userData AS
(
    SELECT 'hasst001' AS UserName , 'Forename' AS ColumnName, 'Tahir' AS Value
    UNION
    SELECT 'hasst001', 'Surname', 'Hassan'
    UNION
    SELECT 'hasst001', 'ComputerName', 'WST0XXXX'
)
SELECT * -- can select result columns e.g SELECT UserName, Forename
FROM userData
PIVOT
(
    MAX(Value)
    FOR ColumnName IN (Forename, Surname, ComputerName)
) AS _pivot

The result:


UserName Forename Surname  ComputerName
-------- -------- -------- ------------
hasst001 Tahir    Hassan   WST0XXXX

The `PIVOT` statement expects an aggregate function, which we use over the value column. Instead of using `MAX` we could have used `MIN`. The only time that the aggregate function comes in, is when we have multiple rows with the same `UserName` and `ColumnName`. Given the dataset below:


UserName ColumnName   Value
-------- ------------ --------
hasst001 ComputerName AAAAA
hasst001 ComputerName ZZZZZ

we expect the `ComputerName` for the user to be `ZZZZZ` because we used `MAX`.

No comments: