Solved

SyBase SQL Query

Posted on 2014-11-13
7
172 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 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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 

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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

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.…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

726 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