Paul White (4/11/2009)

---

2. If the first 3 characters of the zip are to be used in this way on more than an ad-hoc basis, it might be sensible to...

To add to what Flo stated... if you insert more than one row with an IDENTITY column, you can use OUTPUT to capture all of the IDENTITIES created as a...

A well formed crosstab can be almost twice as fast as a Pivot and a whole lot easier to read, to boot. Please see the following articles...

http://www.sqlservercentral.com/articles/T-SQL/63681/ (Performance...

I think you could probably cut way down on the scans and rowcounts if you did a little "Divide'n'Conquer" to do some pre-aggregations to temp tables and join on those.

Of...

Heh... so you know that guy, too, huh? 😀

Heh... no... that's certainly not ring knocking in my book. It takes a certain amount of hair to stand up in front of a worldwide community and speak. ...

You bet... thanks for the feedback, Flo.

sku370870 (4/11/2009)

---

Now, this all works - but it is effectively hard coded. What happens when someone adds Department 5 - or deletes Department 3.

I want to write this in such...

Florian Reischl (4/11/2009)

---

Hi

Try to handle it with a PIVOT operator.

Greets

Flo

PS: Maybe have a look to the "Best Practices" link in my signature for posting sample data and DDL. This always...

mister.magoo (4/11/2009)

---

Jeff Moden (4/11/2009)

---

Oh... be careful folks... this wonderful and great interplay of thoughts and ideas to come up with some absolutely outstanding code all share the same common fault......

I missed one... there is one solution above that takes an IDENTITY column into consideration.

Oh... be careful folks... this wonderful and great interplay of thoughts and ideas to come up with some absolutely outstanding code all share the same common fault... they all can...

The answers about sargeable predicates have already been pretty well covered on this thread, so I'll not go there.

Going back to the original question, the first code snippet uses correlated...

arup_kc (4/9/2009)

---

Why are we using GO statement? Generally SQL statements are executed sequentially, then why r we using GO?

Here is a situatrion where without GO, its throwing an error.

ALTER TABLE...

Apologies for the large delay in responding...

At this point, I'm not sure... guess I'd have to play with it. I'd probably clean all the data so that anything that...