SQL Script | ROW_Number & CASE WHEN

[CharlieWh]

I have a Select & Insert Into statement that I am trying to build and due to duplicates (that must not be removed) I am attempting to use row_number within a case statement, so that when the result of row_number = 1 then I specify the vault of As WsID as one thing and case row_number is grater than 1 then append row_number vault to the text.

 

[arnelgp]

can a Total (aggregate) query do it?

 

[CharlieWh]

A bit like this, but this is no where near

d.c1alias
        ROW_NUMBER() OVER(PARTITION BY d.c1alias ORDER BY d.c1alias)
        CASE
        WHEN d.c1alias = 1 THEN 'Original'
        WHEN d.c1alias = >1 THEN 'Not Original'
        END
        AS Wsid,

 

[Minty]

What version of SQL Server are you using?

 

[CharlieWh]

Got it!

CASE
WHEN ROW_NUMBER() OVER(PARTITION BY d.c1alias ORDER BY d.c1alias) = 1 THEN concat(''PROP.'', d.c1alias)
ELSE concat(''PROP.'', d.c1alias, ''_'', ROW_NUMBER() OVER(PARTITION BY d.c1alias ORDER BY d.c1alias))
END As Wsid,