Renaming results in query

Question

Renaming results in query

vprunic

SSC Eights!

Points: 850
More actions
April 26, 2016 at 6:03 am

#400072

Hello all,
Im not an expert so, still struggling with some issues, and was hoping I could get some help
How would I be able to rename all the members that fall in the same group and add a autoincrease number there?
Ill try to explain:
Col1/Col2/Col3
A1/B1/C1
A2/B2/C1
A3/B3/C3
But I would love to see this as a output
Col1/Col2/Col3
A1/B1/New001_C1
A2/B2/New001_C1
A3/B3/New002_C3
So Col3 Members that have all the same name get renamed so it has 'New'+<autonumber>+'_'+old name
Thank you in advance and sorry if this is way to low level
Vukasin

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246982 More actions · Answer 1

vprunic (4/26/2016)
Hello all,
Im not an expert so, still struggling with some issues, and was hoping I could get some help
How would I be able to rename all the members that fall in the same group and add a autoincrease number there?
Ill try to explain:
Col1/Col2/Col3
A1/B1/C1
A2/B2/C1
A3/B3/C3
But I would love to see this as a output
Col1/Col2/Col3
A1/B1/New001_C1
A2/B2/New001_C1
A3/B3/New002_C3
So Col3 Members that have all the same name get renamed so it has 'New'+<autonumber>+'_'+old name
Thank you in advance and sorry if this is way to low level
Vukasin

IF OBJECT_ID('tempdb..#test', 'U') IS NOT NULL

DROP TABLE #test;

CREATE TABLE #test

(

Col1 VARCHAR(10)

,Col2 VARCHAR(10)

,Col3 VARCHAR(10)

);

INSERT #test

(Col1, Col2, Col3)

VALUES ('A1', 'B1', 'C1'),

('A2', 'B2', 'C1'),

('A3', 'B3', 'C3');

SELECT t.Col1

, t.Col2

, t.Col3

, CONCAT('New', RIGHT('000' + CAST(DENSE_RANK() OVER (ORDER BY t.Col3) AS VARCHAR(3)), 3), '_', t.Col3)

FROM #test t;

--Edit: deleted my original post after working out what was required.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

vprunic SSC Eights! Points: 850 More actions · Answer 2

Hey Phil

Its because the Column3 member named C1 has the same name as the Column3 member from row1

vprunic SSC Eights! Points: 850 More actions · Answer 3

Thank you! It worke! I tried with rank, but I think I didnt really understand it! Thank you so much!

Phil Parkin SSC Guru Points: 246982 More actions · Answer 4

vprunic (4/26/2016)
Thank you! It worke! I tried with rank, but I think I didnt really understand it! Thank you so much!

No problem. Thanks for posting back.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel