Hi, my name is Lutz and I'm from Germany.

Uupps, I'm sorry, wrong intro. :blush:

Hi, my name is Lutz and I'm not a DBA.

😀 SOM 😀 Gorgeous!

In fact, considering the...

I'd do it this way:

1) Group the data by year into a staging table (key1, key2, yearlyAmount, year).

2) Add a column [RunningTotal]

3) Add a clustered index on key1, key2, year...

As a side note: I don't want to start a race between you reposting your question and me adding the above reply. So please stop opening...

Reported as spam.

Change the text data type to XML. This is not only to match data type and data content. You'll also get rid of a deprecated data type.

Once this is done...

Duplicate post.

Discussion started here.

sqlbi (8/27/2010)

---

It doesn't help basically i want it to be in.. wether im doing mistake some where in the script?

...

WOW!!!!

It doesn't happen that often to have somebody posting a question as perfect as you did!!!

There's nothing more to say than GREAT JOB!!

And here's how I'd do it:

SELECT

city,

zip,

SUM(CASE...

Step 1: remove those dozens (or even hudreds) of rather useless lines in your previous post.

Step 2: read the first article referenced in my signature

Step 3: provide ready to use...

I would use a few CTE's (CommonTableExpressions) or maybe even separate queries to poulate temp tables for intermediate data to get the subresults (especially for the pivot/CrossTab part) and join...

WayneS (8/26/2010)

---

Lutz, you done it again! I've learned two things from your post:

1. I thought that you had to reference the nodes as a.c.value, etc. in the column list area...

Maybe it would help if you'd post table def and sample data in a ready to use format as well as expected result. Would save those volunteers around a lot...

Ummm, Jeff... gotta ask a rather stupid question:

Does "One Orange Chip" relates to Roulette or is...

Why do you have to use VB? Pass the path of your xml file to a stored procedure and let SQL Server do the mapping.

But if you really want to...