Solved

SyBase SQL Query

Posted on 2014-11-13
7
160 Views
Last Modified: 2014-11-24
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
0
Comment
Question by:triphen
  • 3
  • 3
7 Comments
 
LVL 11

Expert Comment

by:Dany Balian
ID: 40442290
this is how you want to see them? or this is the main table??
0
 

Author Comment

by:triphen
ID: 40443385
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
 
LVL 26

Accepted Solution

by:
wilcoxon earned 500 total points
ID: 40444064
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:triphen
ID: 40444083
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
 
LVL 26

Expert Comment

by:wilcoxon
ID: 40444099
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
 

Author Comment

by:triphen
ID: 40454051
I dont know perl.

I know VB.net is VS2010...any ideas with that?
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 40454274
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

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now