Excel Style Row Numbering in SQL

February 3, 2014 at 12:59 pm | Posted in Oracle Application Express (Apex), Oracle Developement, Oracle Tools | Leave a comment
Tags: , , , , , , , , ,

Here is a trick to change your rownumbering (or any other sequential numbering) to the way Excel names their columns. The first 26 rows will be named A – Z, the next 26 AA – AZ, then BA – BZ, etc..

SELECT decode( ceil( ( ROWNUM / 26 ) - 1)
             , 0, ''
             , chr( ceil( (ROWNUM/26)-1 ) + 64)
             )
     || chr(64 + decode( MOD(ROWNUM, 26)
                       , 0, 26
                       , MOD(ROWNUM, 26)
                       )
            ) col2
 FROM dual
CONNECT BY LEVEL <= 702; --limits the result to 702 rows 

Result:
Row	Label
---------------
1	A
2	B
3	C
4	D
5	E
	…
28	AA
29	AB
30	AC
31	AD
32	AE
	…
698	ZV
699	ZW
700	ZX
701	ZY
702	ZZ

Note that this will be good for 702 rows (nbr_of_letters * (nbr_of_letters+1)). After that you’d have to concatenate a third character, or write a function.

Advertisements

Leave a Comment »

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Entries and comments feeds.

%d bloggers like this: