Friday, October 31, 2008

PIVOT Is Elegant Solution In Summarizing Data

Project I'm working on has to do with counting how many machines are being used in our environment. In our database, I'm able to list data like the following:

LocationCode MachineType MachineStatus
SJO Desktop Production
SJO Notebook Production
FSK Desktop Factory
TMP Desktop Production
AUS Notebook Production
TMP Desktop Factory
FSK Notebook Production
SJO Desktop Production
FSK Desktop Production

Now I need to provide a summary like as follows:

Category San Jose Fishkill Tempe Austin
Qualified Desktop 2 1 1 0
Qualified Notebook 1 1 0 1
Factory Desktop 0 1 1 0

Here's the solution using PIVOT:

    Category AS [Category],
    [AUS] AS [Austin],
    [FSK] AS [Fishkill],
    [SJO] AS [San Jose],
    [TMP] AS [Tempe],
    [AUS]+[FSK]+[SJO]+[TMP] AS [Total]
    ( SELECT
        WHEN MachineStatus = 'Production' AND MachineType = 'Desktop' THEN 'Qualified Desktop'
        WHEN MachineStatus = 'Production' AND MachineType = 'Notebook' THEN 'Qualified Notebook'
        WHEN MachineStatus = 'Factory' THEN 'Factory Desktop'
    END) AS [Category]
    FROM V_PcCount_Hardware) AS SourceTable
    FOR LocationCode IN([AUS], [FSK], [SJO], [TMP])
) AS PivotTable