?
Solved

SyBase SQL Query

Posted on 2014-11-13
7
Medium Priority
?
186 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1500 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses
Course of the Month13 days, 2 hours left to enroll

777 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