CODEWITHSUNDEEP
sql-servert-sql
user1497569
user1497569

Reputation: 93

create rank() or row_number() column

I have table like below,

enter image description here

If we check "n" column, its double - same if Question is repeating with multiple answers separated by ansSecID.

QuestionId + AnsSecId = unique row

Can I create rank() column for "n". e.g. n = 8, has 2 rows. So i want 8_1, 8_2.

or

for n = 17, can I get 17_1,17_2,17_3

Or something like rank() column. some 1,2,3 row no. for repetitive n.

max will be 3 answers for 1 question.

Upvotes: 0

Views: 82

Answers (2)

Ab Bennett
Ab Bennett

Reputation: 1432

Try this, I am not on a computer but this should work. Could also use n for the partition

select Cast (n as varchar)+'-'+cast (r as varchar) str
from (
     Select n, row_number() over (partition by q_id order by anssecid) r
     from table
     ) x

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Something like this:

select (cast(n as varchar(255)) +
        (case when count(*) over (partition by n) > 1
              then '_' + cast(row_number() over (partition by n order by anssecid) as varchar(255))
              else ''
         end)
        ) as new_n

Actually, your repeat column seems to do the work for the when:

select (cast(n as varchar(255)) +
        (case when repeat > 0
              then '_' + cast(row_number() over (partition by n order by anssecid) as varchar(255))
              else ''
         end)
        ) as new_n

Upvotes: 2

Related Questions