Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Row number formate

回答済み Row number formate

  • Monday, January 28, 2013 6:29 AM
     
     
    Sign In to Vote
    0

    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
     
     
    Sign In to Vote
    0
    what is the logic behind this?

    Regards
    Satheesh

     
  • Monday, January 28, 2013 6:54 AM
     
     Answered Has Code
    Sign In to Vote
    0
     
    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
     
     
    Sign In to Vote
    0

    Hi,

    thanks for reply

    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
     
     Answered Has Code
    Sign In to Vote
    1

    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
     
     
    Sign In to Vote
    0

    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 Eswararao C Monday, January 28, 2013 10:46 AM
    •  
     
  • Monday, January 28, 2013 11:06 AM
     
     
    Sign In to Vote
    0

    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 Hari tha Monday, January 28, 2013 12:06 PM name
    •  
     
  • Monday, January 28, 2013 11:28 AM
     
     
    Sign In to Vote
    1

    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 Eswararao C Monday, January 28, 2013 11:29 AM
    •