Creating alphabetical sequence by grouping in SQL

I have a project for our shipping department - I need to created in SQL query that will provide the following results;
1. group by style & color (example: style:123R12, color: BLK) and create an alpha sequence (A) which 123R12BLK = A

2. if style is the same but color is different (example: style:123R12, color: BLUE) it should be sequence (B) which 123R12BLUE = B and so on.

3. Sequence could go as far A thru ZZZ
Rafael GuerraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Sounds like you need a cross-reference lookup table that assigns a alpha value based on model and color.

Otherwise, what is the logic that says a Black  123R12 is an 'a' and a Blue one is a 'b'?  What would a zebra striped one be?
Rafael GuerraAuthor Commented:
hello, the sequence alpha (A) is based on the group by style&color, I will create a lookup table by style& color but my question is how do I assigned a sequence in alpha like A, B, C, thru zzz
Ryan ChongSoftware Team LeadCommented:
found this function online:

create function dbo.HinkyBase26( @Value as BigInt ) returns VarChar(15) as
  begin
  -- Notes: 'A' = 0.  Negative numbers are not handled.
  declare @Result as VarChar(15) = '';

  if @Value = 0
    select @Result = 'A';
  else
    set @Value += 1;
  while @Value > 0
    select @Value -= 1, @Result = Char( ASCII( 'A' ) + @Value % 26 ) + @Result, @Value /= 26;
  return @Result;
  end;

Open in new window


which can be applied as:

;with grp as
(
	Select ROW_NUMBER() over (order by style, color) idx,
	style, color
	from yourTable
	group by style, color
)
Select dbo.HinkyBase26(b.idx - 1) RunningChars, a.style, a.color
from yourTable a
inner join grp b on a.style = b.style and a.color = b.color
order by a.style, a.color

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

PatHartmanCommented:
I think we are not understanding the purpose of the alpha assignment.  Why wouldn't a number work?  Who decides whether blue sorts before green?  Would sorting by the color name work?  What is the exact data we are working with?  Please give us a sample with the transformation you want.
slightwv (䄆 Netminder) Commented:
>> but my question is how do I assigned a sequence in alpha like A, B, C, thru zzz

Assuming you ALWAYS want a Black 123R12 to be 'A',  it will need to be manually generated in the lookup table.

Otherwise you will need to define the rules that will ALWAYS generate the same value at the same time.

It probably can't be an alphabetic sort because someone will create an Azure 123R12 and it will become the new 'A'.
Scott PletcherSenior DBACommented:
Create a table to convert numbers to an alpha sequence:
(1, A), (2, B),...,(27,AA),...

Then use this query:

SELECT qry1.style, qry1.color, a.alpha_sequence
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY style ORDER BY color) AS row_num
    FROM dbo.table_name
) AS qry1
INNER JOIN dbo.alpha_sequences a ON a.seq_num = qry1.row_num

A table lookup will be vastly faster than a function and easier to maintain and customize (for example, often I and O are left out of such sequences because they look to much like numbers).
slightwv (䄆 Netminder) Commented:
>>Then use this query:

That will work unless you need the generated sequence to ALWAYS be what it once was.  If Black was 'A' a year ago and has to always be 'A', using row_number will fail when someone adds Azure.
Scott PletcherSenior DBACommented:
True, but I didn't take the requirement that way.  I took it as 'A' for that query.  As long as the query has a unique sequence, you're fine.

I could very easily be wrong here.  But, if it were intended to be permanent, it would technically be more properly called an "id" rather than a "sequence".
Rafael GuerraAuthor Commented:
The reason for Alpha character and NOT number sequence - see below explanation:
we have packing areas and each area works with different customer register order numbers; a customer could have multiple register(s)# that can have multiple deliveries within the same month (8/15,8/20,8/25, etc.).
example: Customer: AAA with register 12345 and  delivery august 15 with 100 styles and 3 different colors for each style.
each style and color can be pack with different prepack combination(s=1,m=2,l=2,xl=1) based on the customer requirements for each store.
currently we are exporting the customer register# to excel and sorting it by style, color and manually assigning alpha sequence letter, by doing this hideous work we prevent shipping wrong prepacks to the customer.  please let me know if you need more inf.
slightwv (䄆 Netminder) Commented:
>>please let me know if you need more inf.

Does the same sequence have to be generated over time.  For example:  Black generates 'A' today.  MUST it ALWAYS generate an 'A' going forward or does the sequence need generated for that specific order?

So order 101 Black is 'A'.  In order 102 can Black be 'B'?

>>Alpha character and NOT number sequence

Didn't see a reason for alpha over number but that's OK.
PatHartmanCommented:
I also don't see a reason for alpha over numeric.  It just adds a step to your computation.  I did have to solve a similar problem for a manufacturing company for identifying Heats (a single batch of a metallic alloy.  We needed to be able to identify all the components that were built from that batch of metal if any component failed)  Apparently they call it a "heat" rather than a "batch" because you have to melt stuff to create it.

To do this you need to manually do the arithmetic yourself.  The letters a-z translate to 26 values so for the 27th, you end up with two alpha digits - AA.  AA-AZ = 27-52 and 52 causes a carry so that the left most A becomes B. etc.  When you add 1 to ZZ, you have to make a third digit.  ZZ +1 = AAA.

If you have only three digits to worry about, you have 26*26*26 values or 17,576 values.  You can use generated numbers and a lookup into this table or you can do the arithmetic going right to left, character by character and when the number exceeds 26, you carry.  It's like base 26 arithmetic rather than base 10.  It was at least 40 years ago that I wrote this code and it was in COBOL (which is similar enough to VBA that it would translate easily but I obviously don't have the actual code.

If your current value is ABB, you would convert the second B to 2 and add 1 so the next number is ABC.
If your current value is ABZ you would convert z to 26 and add 1.  Since 27 is > 26, you make the position you are working on A and move one character to the left where you convert B to 2 and add 1 making C so you get ACA

Is that how you think it should work?
Scott PletcherSenior DBACommented:
Again, I'd strongly urge use of a conversion table rather than trying any kind of calc'd translation, because certain letters are often excluded from such strings because they look too much like numbers, such as "I" or "O".
Rafael GuerraAuthor Commented:
thank you for your help to everyone and support
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.