Row number formate

# Row number formate

• Monday, January 28, 2013 6:29 AM

Hi,

i need S.NO as shown in below formate can anyone help me...

S.No      Field

1           AA

a           AAB

b           AAC

2           BB

3           CC

a           CCA

b           CCB

Regards,

Haritha.

### All Replies

• Monday, January 28, 2013 6:32 AM

what is the logic behind this?

Regards
Satheesh

• Monday, January 28, 2013 6:54 AM

```Select Case when Len(Field)=2 then Convert(varchar(8),ROW_NUMBER() over(partition by len(Field) order by Field))
else Char(97 + ROW_NUMBER() over(partition by len(Field) Order by Field)-1)end as rownumber,*from Test_ordering
Order by Field```

Try this

I have done according to the length of the field.

Regards,Eshwar.

Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

• Monday, January 28, 2013 10:23 AM

Hi,

i tried this it shows "a" for all subfields value...

Is thre any another way to solve this...

regards,

haritha.

• Monday, January 28, 2013 10:39 AM

try this

```declare @tab table(SNo varchar(50),   Field varchar(50));
insert into @tab(Field) values
('AA'),
('AAB'),
('AAC'),
('BB'),
('CC'),
('CCA'),
('CCB');

with cte as (Select *,ROW_NUMBER()Over(Partition by substring(field,1,2) Order by field)RN
from @tab)
select Field,
case when RN=1 then CAST(1 as varchar)
else CHAR(95+RN)end Row_NUM
from cte```

what happens after CCB,CCC,.....CCZ?

Regards
Satheesh

• Monday, January 28, 2013 10:44 AM

If this is the data in the table and want to see serial numbers according what you have shown above then it should work. If this is not the requirement then it will not work because you should be clear in explaining the requirement.

"i tried this it shows "a" for all subfields value..." where is the subfields in the source table?

Regards,Eshwar.

Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

• Edited by Monday, January 28, 2013 10:46 AM
•
• Monday, January 28, 2013 11:06 AM

Hi,

Thanku Sathish Varirath for giving reply...

In ROW_NUMBER previously im using order by now i changed as partition.

Now its working fine....

Regards,

Haritha..

• Edited by Monday, January 28, 2013 12:06 PM name
•
• Monday, January 28, 2013 11:28 AM

Please mark as answer if it helped in resolving the issue keeping in mind of future readers.

Regards,Eshwar.

Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

• Edited by Monday, January 28, 2013 11:29 AM
•