Without looking too deeply, because I don't have the time right now. You should get a performance boost by removing the ORDER BY's

Good catch Sergiy 😛

No need to PIVOT or CROSSTAB, even though they'll both work ....

-- create a table for sample data

DECLARE @t TABLE

(RowID Char(16)

,Code1...

if you later have

table1.col4 = table2.col4

in the where clause then it's an inner join

Nope, in that use they are equivalent.

Please post the code you've tried.

Matt Miller (5/14/2008)

---

By the way - Jason has a small typo (the source of your errors) in his 2000-compatible version:

SELECT

a.name

,reg_hrs

...

Under Properties > Options for the database. But if it's SQL 2000, the CTE will not work.

Try this instead.

SELECT

a.name

,reg_hrs

...

Are you using SQL 2005?

Compatibility Level set to 90?

Place a semi-colon at the end of the line immediately before the WITH statement.

I'm not sure I understand why you want a "variable" Are you trying to avoid performing the aggregation more than one time?

If so then ....

WITH totals

AS (SELECT

...

The model database is "copied" when you create a new database. So if you create a table in it, such as a tally or numbers table. Then all subsequent databases...

Search for SPLIT function on this site. You'll most likely need a variation of that.

Lookup LTRIM in BOL.

Helen Thorley (5/13/2008)

---

Hello,

This works perfectly for me when run once - but won't "re-run".

Every subsequent attempt results in an report status of 'Root element is missing'.

I've checked the Subscriptions and...