By Kevin Jobe on Wednesday, March 12, 2008 10:58 AM

Another friend in SQL is the CAST function.  Assume you need to join 2 tables on columns that have different data types, and the types are not compatible.  Use the CAST function in your join statement:

SELECT     EADP_AppDB.dbo.D_Resource.MemberId AS ResourceMemberID, dbo.vEADP_VistaReadyTransposed.ResourceID,
                      dbo.vEADP_VistaReadyTransposed.KPIName, dbo.vEADP_VistaReadyTransposed.KPI, dbo.vEADP_VistaReadyTransposed.KPIMemberID

FROM         EADP_AppDB.dbo.D_Resource INNER JOIN
              ... Read More »

By Kevin Jobe on 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&# ... Read More »