That code is not valid.  There is no way to tell which 'type' column is being referenced in the WHERE clause.

You have a some options at least to help speed things up.  I'm sure someone can think of others.  (Entries are numbered only to ease possible discussion of them, not...

You're doing (the equivalent of) a CROSS JOIN on the WORKC and PLNT_WORKC tables (JOIN ON 1 = 1).  That could generate a huge number of rows.  You then filter...

You don't need a temp table, you can just create a view of the existing table.  The really good this is the view can be created using dynamic SQL, so...

You can defer the ON clause for a JOIN until after other JOINs.  Normally you wouldn't do that unless it's absolutely necessary.

LEFT OUTER JOIN TABLEC CC ON (CC.hFKey4 = CHG.hPKey...

Thank you all!

Sorry but I made a mistake on my original posting. Revised DATA representation is below

IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL
 ...

WHERE t_stamp >= (CAST(DATEDIFF(SECOND, '19691231 20:00:00', GETDATE()) AS bigint) * 1000) - (1000 * 60 * 30)

ARPRINCE,

Have you looked at the query plans for the two queries?  The LEFT JOIN method is doing a full table scan per join.  That could be a (big) performance issue,...

SELECT 
    ID,
    MAX(CASE WHEN Referrence = 'OrderNo' THEN Value END) AS OrderNo,
    MAX(CASE WHEN Referrence = 'OrderDate'...

Here's the "standard" cross-tab method:

SELECT 
    ID,
    MAX(CASE WHEN Referrence LIKE 'ON%' THEN Referrence END) AS OrderNo,
    MAX(CASE...

The use of the prefixed "TBL_" is a design error so bad it has a name; it's called the tibble.

This is not a design error.  It does not violate...

I use

;WITH

just to help prevent possible errors.  Sure, I could be technically correct and blame someone else when there was a problem with the code, but I prefer to avoid...

I would not prefix a view with tbl.  In any case I rarely use views in an OLTP environment.  I do make extensive use of them in...

I would not prefix a view with tbl.  In any case I rarely use views in an OLTP environment.  I do make extensive use of them in my OLAP...

I believe that SQL renames temp tables rather than dropping them, esp. for stored procs.  That way, when the proc runs again, it doesn't have to fully recreate...