imo you should avoid mixing collations unless there is no way you can avoid it. If you have mixed collations, but there is no real need for it, it's better...

In that case an option may be to nest derived tables. That way you only have to define the expression once, then you can refer to it's label in the...

You have to change the expressions from the lables to the expressions that evalutes to those labels..

Select

TYPE,

TYPE2,

ATTRIBUTE1,

LOCATION =

 CASE

WHEN (TYPE = 'Switch' AND TYPE2 = 'out') THEN 'inwards reversal'

WHEN (TYPE =...

ummm maybe.. dunno.. was very long time ago. This 'magic pivot' has been around since 6.x days. I only remember that once there was an 'issue' when doing it, and...

I'd say that the easy answer is 'it depends'.

imo there is never going to happen that you can write a piece of generic code...

Ah... (had my coffee now) Something like this?

use northwind

select customerId,

       'Customers' as 'origin'

from   customers

union all

select customerId,

       'Orders'

from   orders

customerId origin   

---------- ---------

ALFKI      Customers

ANATR      Customers

ANTON      Customers

..................

ANATR      Orders

ANATR      Orders

ANTON     ...

Nope, that was not it - that much I remember

Anyway, I just mentioned it as a 'standard disclaimer' - something to keep in...

In SQL Server 2005 we will have PIVOT.

I don't remember the specifics about when this trick didn't work, it had something to do with indexes, that much I know. But,...

Maybe not enough coffee, but I don't get it..

Can you explain what you're trying to do?

/Kenneth

So, are you saying that by making the user sysadmin, the problem is solved?

I would argue that that is not a solution at...

In this case, the function is the 'loop'.

The function just encapsulates the 'looping logic' needed to pivot the tradenames.

Though, be aware that (afaik) this 'pivot-trick' isn't supported, and if memory serves...

Ok, so then the problem is that sometimes the dbnames in 'old' and 'new' have different collations. Assuming you can't fix the root of the problem (making all collations the...

Here's an alternative way that may be easier to read, and also avoiding negations. Of course one has to look at the actual queryplans (and possibly i/o statistics) in order...

Starting to clear but still misty

Ok, so if we back off a little.. Can you post a part of the actual code that...

Not exactly your situation, but the info may give some pointers where to start looking. http://support.microsoft.com/default.aspx?scid=kb;EN-US;328569

/Kenneth