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
    SELECT 'hasst001', 'Surname', 'Hassan'
    SELECT 'hasst001', 'ComputerName', 'WST0XXXX'
SELECT * -- can select result columns e.g SELECT UserName, Forename
FROM userData
    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: