SyBase SQL Query

Hello Experts,

I have a unique situation where I need to group by a column but the column has the data stored in a way where I cant figure out how to group by. The DB table in question contains employees and which store they have access to (S1 = Store1, S2 = Store 2, S17 = Store17, etc). I want to see all employees grouped by stores they have access to. As an example, employee "KRISHNA C." would have be apart of stores: 1, 4, 5, 6, AND 7. (see attached)

This is SyBase SQL Anywhere v10
table.png
triphenAsked:
Who is Participating?
 
wilcoxonConnect With a Mentor Commented:
The main table contains the stores as a single combined string?  That does make it hard.

If there are a reasonably small number of different stores, you could do something like:
select 'S1' store, name from main_table where stores like '%,S1,%'
union all
select 'S2' store, name from main_table where stores like '%,S2,%'
-- etc

Open in new window

0
 
Dany BalianCTOCommented:
this is how you want to see them? or this is the main table??
0
 
triphenAuthor Commented:
This is the main table.....

I want to see (as a crude example)


S1
KRISHNA C.
ELNA
JUNE

S4
KRISHNA C.

S5
KRISHNA C.

S6
KRISHNA C.

S7
KRISHNA C.
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
triphenAuthor Commented:
The main table contains the stores as a single combined string?  That does make it hard.


Yes Exactly!

This worked, but I imagine there has to be a better way. Currently there are 20 stores, but there will be more. Any other ideas?
0
 
wilcoxonCommented:
I don't have a better idea in pure sql.  Personally, I'd write a perl script to process this data and generate the output you want (any language with decent string handling should work).

This should work except for the $connect_string and real table name:
use strict;
use warnings;
use DBI;
my $dbh = DBI->connect($connect_string) or die $DBI::errstr;
my $sth = $dbh->prepare("select name, stores from main_table");
$sth->execute;
my %stores;
while (my $row = $sth->fetchrow_arrayref) {
    foreach my $store (split /,/, $row->[1]) {
        next if ($store =~ /^\s*$/);
        $stores{$store} = [] unless $stores{$store};
        push @{$stores{$store}}, $name;
    }
}
$sth->finish;
foreach my $store (sort keys %stores) {
    print $store, ":\n", join("\n", @{$stores{$store}}), "\n\n";
}

Open in new window

0
 
triphenAuthor Commented:
I dont know perl.

I know VB.net is VS2010...any ideas with that?
0
 
wilcoxonCommented:
It's been something like 15 years since I coded in VB (pre-.Net).  I've thought about learning C# but haven't gotten around to it yet...
0
All Courses

From novice to tech pro — start learning today.