Mar 8

Written by: Kevin Jobe
Saturday, March 08, 2008 7:17 PM

 Often, you will need to transpose columns to rows in order to deliver data to the staging db or fact table.  When this need arises, the UNION ALL operator in SQL is your friend.

The following statement will accomplish this for you: (Table examples can be found by clicking the "more" link below)

SELECT     ResourceID, 'OSStatus' AS KPIName, OSStatus AS KPIValue
FROM         dbo.ViewOrTable
UNION ALL
SELECT     ResourceID, 'CPUStatus' AS KPIName, CpuStatus
FROM         dbo.ViewOrTable AS ViewOrTable_1
UNION ALL
SELECT     ResourceID, 'ProcessorStatus' AS KPIName, ProcessorStatus
FROM         dbo.ViewOrTable AS ViewOrTable_1

Assume you have data in the following table format:

ResourceID	OSStatus	CPUStatus	ProcessorStatus
1	OK	FAIL	OK
2	OK	OK	FAIL

And you need the data structured as follows:

ResourceID	KPIName	KPIValue
1	OSStatus	OK
2	OSStatus	OK
1	CPUStatus	FAIL
2	CPUStatus	OK
1	ProcessorStatus	OK
2	ProcessorStatus	FAIL

The following statement will accomplish this for you:

SELECT     ResourceID, 'OSStatus' AS KPIName, OSStatus AS KPIValue
FROM         dbo.ViewOrTable
UNION ALL
SELECT     ResourceID, 'CPUStatus' AS KPIName, CpuStatus
FROM         dbo.ViewOrTable AS ViewOrTable_1
UNION ALL
SELECT     ResourceID, 'ProcessorStatus' AS KPIName, ProcessorStatus
FROM         dbo.ViewOrTable AS ViewOrTable_1

Hope this helps..  It can and will be used frequently..

Tags: