It's not clear to me what you're looking for.

What rows do you want to return?

Simple scenario:

col1col2

A11

A12

A23

A24

What would be your expected output?

There are several concepts depending on what type week number you're looking for.

If you need the week numbered as per the restrictions described in BOL (BooksOnLine, the SQL Server help...

You're right, as already explained a few posts back.

Here's the approach I would use.

Please note that I had to add an identity column to the source table just like David suggested. In my first reply I assumed the...

If you start using one of the ideas posted in the challenge I referred to and you get stuck, let us know what you're struggling with and I'm sure there...

If you would post the execution plans for both queries it would help us a lot to see what changes need to be made to tune the code. 3 seconds...

Please provide table def, sample data and...

Why would you do such formatting server side and not client side? Stuff like this usually should be done by the frontend app.

But if you're forced to do it, look...

Hmmm...

It doesn't really make sense to populate a separate table just to exclude 40 rows...

I usually use table variables to populate sample data when answering a forum question since one...

caseyo (7/15/2010)

I did not consider to normalize and properly index your tblResults table. I'll have to look up tomorrow on how to do that. I'll also submit the...

duplicate / misplaced post.

Plese continue discussion here.

I'd rather use

SELECT vague_stuff,

((ROW_NUMBER() OVER (ORDER BY something)-1) / 50 + 1 ) AS box_nbr

FROM NamelessFoobar;

Otherwise your...

You might want to start with the UNPIVOT statement (see BOL aka BooksOnLine, the SQL Server help sytem for details).

As a next step you should have a look at the...

It depends on your table design.

Here's a rather simple example using the sample data you provided:

DECLARE @tbl TABLE

(

article_id INT,

received INT,

age INT

)

INSERT INTO @tbl

SELECT 1,30 ,...

Since you're dealing with a typed xml document, you'd need to declare the namespace(s) defined within that document:

;WITH xmlnamespaces(

DEFAULT 'http://www.portalfiscal.inf.br/nfe'

)

SELECT

R.Node.value('(natOp/text())[1]','varchar(100)') AS natOp,

R.Node.value('(cNF/text())[1]','varchar(100)') AS cNF

FROM @XML.nodes('//././ide') R(Node);