Here's an article in support of Vladan...

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1124064,00.html?FromTaxonomy=%2Fpr%2F301354

Here's the technique I use more often than not when pivoting server-side: http://www.sqlteam.com/item.asp?ItemID=2955

Doing the pivot client-side can often be a good solution too.

Obviously use the technique which works best for your situation.

Hi Neel,

The function method given in the link provided by zh is usually considered best for this type of problem. Here's that method applied to the example given...

Hi all,

I see you have a solution, so this is just FYI. You can use newid() to generate random numbers/dates for each row in a table...

select dateadd(ms,...

This is about as close as you're going to get, but you're better off just doing a normal join and then doing the formatting in your front-end application.

If it appears...

Ah - thanks Jeff!

So it's looking like the answer to which is fastest is 'it depends'. So maybe I do have to test...

That's great - thanks Kenneth!

I think I must've suspected as much, which is why I had a note to change my function (or...

Hi Simon,

There's nothing wrong with that query (except for the typo ).

What indexes do you have on the tables? What is the query plan...

You might want to look into 'linked servers'. Here's one article on them...

http://www.databasejournal.com/features/mssql/article.php/3085211

http://support.microsoft.com/default.aspx?scid=kb;en-us;306625

Hi John,

See http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx - point 13.

Here's that technique applied to your situation...

--data

declare @t table (ID int, PostDistrict varchar(10), Type...

Hi all,

I have a function which is uncannily like JeffB's with Jeff Moden's modification. I won't post it because it's pretty much the same.

Here are a few other options. The middle one would be my favourite.

--data

declare @staff table (NINumber varchar(10), department int)

insert...

Pam beat me to it, but here's something to run to see the issue. As Pam says, this is entirely normal behaviour. You just need to be aware of nulls! 

Here are 2 alternative functions. The first is similar to Vladan's (but slightly simplified), and the second is recursive.

I've not done any speed tests, but I'd be interested in the...