Great tip Jeff. Thank you for sharing this. I remember this discussion is coming up for the second time. I appreciate your comments and am glad to learn something new...

GSquared,

I agree that a number table should be the first choice. The approach presented in this article is useful for cases when you do not want to use a number...

agreed!

Restored it !

You can find all articles in this series here: http://www.sqlserverandxml.com/2007/11/sqlce-workshop.html

Hi Matt,

We are getting this fixed;

thanks

Jacob

You will need Visual Studio Standard, Pro or Enterprise. This link will give you more info: http://blogs.msdn.com/stevelasker/archive/2007/08/07/configuring-visual-studio-2005-for-use-wtih-sql-server-2005-compact-edition.aspx

I agree with most of you, especially Jeff, Steve, Gila. Out of curiosity I sent this question to Itzik Ben-Gan to see what thinks about it. Here is a summary...

I think some times people expect the FORUMS to give them the FINE results which saves them all the work. It looks like they dont want to learn fishing, but...

Hi Jeff,

I must admit that my previous statement did not turn to be 100% correct. I just created a test table and populated some test data. I did some testing,...

I agree with Jeff that PIVOT operator is not that useful in the specific task that we saw. Infact, it is not very helpful with variable number of columns.

However, i...

WHERE is evaluated before GROUP BY. Hence you cant use it in WHERE. A CTE will help to simplify the code.

this might help you:

http://www.codeproject.com/KB/aspnet/Schedule__Run__SSIS__DTS.aspx

Modify the definition of the temp table

CREATE TABLE #tmp (Value NVARCHAR(200), Data NVARCHAR(500) NULL)

I guess we need to know the columns in advance to do a pivot operation. Probably you should make a list of countries and add it in the FOR() clause.

[font="Courier...