add a counter to a SELECT query's results

Question

I'm trying to add a counter to a list of Select results. Keeping it simple, my query is this:

Select distinct '"'+cast((product_sku) as varchar(30))+'"' as product_sku,preco,'"tamanhoecor::' + Left(Main.preco,Len(Main.preco)-1) + '"' As "custom_price"
From(
Select distinct ST2.product_sku,

        (Select cordesc + ' - ' + tamanho +':' + cast(price_dif as varchar) + ';'
        From [mg_produtos_preco] ST1
        Where ST1.product_sku = ST2.product_sku 
        ORDER BY ST1.product_sku
        For XML PATH ('')) [Preco],
        product_price, cordesc,tamanho
        From [mg_produtos_preco] ST2) [Main]

Which gets me this:

product_sku     preco                                                custom_price
"340803 010"    Preto - S:0;Preto - M:0;Preto - L:0;Preto - XL:0;    "tamanhoecor::Preto - S:0;Preto - M:0;Preto - L:0;Preto - XL:0"
"340803 100"    Branco - S:0;Branco - M:0;Branco - L:0;              "tamanhoecor::Branco - S:0;Branco - M:0;Branco - L:0"

However, I need this:

product_sku     preco                                                      custom_price
"340803 010"    Preto - S:0:0;Preto - M:0:1;Preto - L:0:2;Preto - XL:0:3;  "tamanhoecor::Preto - S:0:0;Preto - M:0:1;Preto - L:0:2;Preto - XL:0:3"
"340803 100"    Branco - S:0:0;Branco - M:0:1;Branco - L:0:2;              "tamanhoecor::Branco - S:0:0;Branco - M:0:1;Branco - L:0:2"

I've tried to use what I've found here: http://msdn.microsoft.com/en-us/library/ms187330%28v=sql.105%29.aspx

I've tried a DECLARE @pos nvarchar(30) Select .... (@pos + = 1) .... from .... (SELECT @pos=0) ....

but I get a "Incorrect syntax near 'DECLARE'. Expecting '(', SELECT, or WITH.", plus a "Incorrect syntax near ="

I tried this code, which worked:

GO
DECLARE @var1 nvarchar(30)
SELECT @var1 = 'Generic Name'
SELECT @var1 = (
SELECT AppUserName
FROM [AppUsers]
WHERE AppUserID = 1000)
SELECT @var1 AS 'Company Name' ;

I would appreciate any help.

Thanks.

ROW_NUMBER works like a charm. I'm just dealing with some roundings and I'll post the solution. Very easy, after knowing the function's existence :) thank you very much! — Mike, May 04 '12 at 10:08

score 3 · Accepted Answer · answered May 04 '12 at 09:49

3

what do you mean by counter? Do you want a incremental number per row? If yes, try this:

select row_number() over (order by your_column), * 
from your_table

answered May 04 '12 at 09:49

Diego

34,802
21
91
134

Thank you, Diego. Mark beat you to the punch, but that's exactly it. I'd upvote if I had the reputation. I'll be posting the full solution in a minute. – Mike May 04 '12 at 10:12
I'm not sure if I should answer my own question, but I'll leave it as a comment: edit: turns out there aren't enough characters available. – Mike May 04 '12 at 10:38
What do you mean by there arent characters available? – Diego May 04 '12 at 10:47
the available number of characters is insuficient for me to paste the solution inside a comment. – Mike May 04 '12 at 10:49
Sorry. I need 15 reputation. Perhaps if you upvote my own answer to my own question here: http://stackoverflow.com/questions/10090734/super-product-attributes-configuration-in-magento-via-magmi – Mike May 04 '12 at 11:25
I meant to mark as answer. Its the green check bellow the voting. It tells the question has already been answered so people dont have to worry about it anymore. You can do that with less that 15 rep. – Diego May 04 '12 at 12:37

add a counter to a SELECT query's results

1 Answers1