Ms sql, how to add 2 new columns using autonumber as their counter? in query

Question

here is the link of the picture:

help i need to create something like table 3..

i already have a query that has them all except for the count2 column, i don't know how to create that in query..

here's my code in query:

SELECT a.rn,'',a.id, b.iddesc 
INTO #x 
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [desc]) AS rn, * 
    FROM aa
) a, bb AS b 
WHERE b.idcon = a.id

SELECT * 
FROM #x

sorry i don't know how to explain it well,

Answer 1

Try this one -

Query:

DECLARE @table_a TABLE (id INT, [desc] NVARCHAR(50))
INSERT INTO @table_a (id, [desc])
VALUES 
    (221, 'aaa'),(222, 'sss'),
    (223, 'ddd'),(225, 'fff')

DECLARE @table_b TABLE (idcon INT, iddesc NVARCHAR(50))
INSERT INTO @table_b (idcon, iddesc)
VALUES 
    (221, 'zxc'),(221, 'sad'),
    (221, 'fdfg'),
    (222, 'asd'),(222, 'vcx'),
    (223, 'zxc'),(223, 'asd'),
    (224, 'cxv'),(224, 'asd'),
    (225, 'zcx'),(225, 'asd'),
    (225, 'qwe'),(225, 'wer')

SELECT 
      idcon
    , [desc] = iddesc
    , count1
    , count2 = ROW_NUMBER() OVER (PARTITION BY id ORDER BY [desc]) - 1
FROM (
    SELECT *, count1 = ROW_NUMBER() OVER (ORDER BY id)
    FROM @table_a
) a
JOIN @table_b ON id = idcon  

Results:

idcon   desc   count1  count2
------- ------ ------- -------
221     zxc    1       0
221     sad    1       1
221     fdfg   1       2
222     asd    2       0
222     vcx    2       1
223     zxc    3       0
223     asd    3       1
225     zcx    4       0
225     asd    4       1
225     qwe    4       2
225     wer    4       3