Link to home
Start Free TrialLog in
Avatar of pepps11976
pepps11976

asked on

Grouping Codes In Sql

Hi All

I have a table in Sql which consists of 293 Analysis Codes ie:

aa12345
aa123456
bb123
cc3456

What i need to be able to do is to group them in to certain products so for example

aa12345 and aa123456 need to be grouped and bb123 and cc3456 need to be grouped

so then i could create a bar graph for products showing which type of product has been sold.

so diodes for instance would consist of the aa12345 and the aa123456 anaylysis codes.

I am Using SQL Server 2008 Standard Edition

Any help on how to do this would be great.

Thanks

John
Avatar of Aaron Shilo
Aaron Shilo
Flag of Israel image

hi

if i got you currect you want to order the values in groups not do a simple aggragate but any way.

1.
select analisyscodes from (
                                              select analisyscodes,
                                              case substring(analisyscodes,1,2) when 'aa' then 1
                                                                                                       when 'bb' then 2
                                                                                                       ....
                                                                                                       ....
                                                                                                       when 'zz' then 24 end ord ) as Tab
order by ord ASC

2.
select count(*) , substring(analisyscodes,1,2)
from table
group by substring(analisyscodes,1,2)
order by count(*) desc

                                                   
Groping codes sounds much more interesting then Grouping codes.....:)

However....
Do you have a table that contains the relationship between the codes and the groups you want to create?

You stated that :
aa12345 and aa123456 need to be grouped and bb123 and cc3456 need to be grouped
If this is correct and not a typo, then ashilo's response does not quite work for you since it will not group bb... and cc.... codes together.

What is it that determines that bb123 and cc3456 should be grouped together?
Avatar of pepps11976
pepps11976

ASKER

So at the moment i have this

SELECT DISTINCT it_anal
FROM         dbo.itran

which gives me this

AAMCSAMP
AERFERRI
AEROWEIN
CONYYREW
HERTUY
OUTGYU

for the above i would need AAMCSAMP,AEROWEIN grouped, And HERTUY and OUTGYU grouped

itried your example but it failed i probably have the syntax incorrect
ASKER CERTIFIED SOLUTION
Avatar of darren-w-
darren-w-
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
hi

i would have to agree with @darren-w-
if there is no simple vivible corraletion between the values then you will have to create a refference table.
Ok Guys i will follow the instructions of Darren-W and see how i get on.

Sorry about the Title.