Solved

Sybase group by

Posted on 2007-11-20
6
825 Views
Last Modified: 2012-06-21
I have to join two tables on a column name
1) table has

Name      Group      Account      
John      1      1      
John      1      2      
Harry      1      3      
Smith      2      4      

table 2)
Name      Address
John      NJ
John      NY
Harry      CA
Smith      MO

and group similar groups together. If I use group by it doesn't provide right results. Please advise.
0
Comment
Question by:saibsk
[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
  • 2
6 Comments
 
LVL 39

Expert Comment

by:Adam314
ID: 20323519
What results do you want?
0
 

Author Comment

by:saibsk
ID: 20323542

Name      Group      Account      Address
John      1      1      NJ
John      1      2      NY
Harry      1      3      CA
Smith      2      4      MO
0
 

Author Comment

by:saibsk
ID: 20323552
I want to write to a file in this format. I want to group all the accounts of same group number together when I write to the file. I cannot use group by when I write the query. It is providing confusing results. I am using another table to join for generating the above result.

#Group 1 Accounts
John, 1, 1, NJ, 2, NY
Harry, 1, 3, CA

#Group 2 accounts
Smith, 2, 4, MO      

I could write it to the file but I want to write similar groups together something like above.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 39

Expert Comment

by:Adam314
ID: 20323598
How do you know John with Account 1 is the NJ address an not the NY address?

To get the results ordered by group, use
    ORDER BY Group
0
 
LVL 19

Accepted Solution

by:
grant300 earned 500 total points
ID: 20323729
The GROUP BY is doing exactly what it is supposed to do.  The problem is that a GROUP BY, while necessary, is not adequate by itself.  You are doing more than that; you are also doing a pivot to stack multiple records with the same NAME in the same row.

The bad news is that this not supported directly in SQL.  No way in a single query.

The good news is that, within certain constraints, there are tricks you can do to get something like the result you want.

Given that you are trying to do this from Perl, I would recommend you get the raw query results you need from the database and do the funky formatting in Perl.  Break processing and stacking string values is probably easier for you to do there than it would be for us to introduce a bunch of complex SQL and stored procedure constructs.

The SQL gets pretty simple; no group by required...

SELECT A.NAME, A.GROUP, A.ACCOUNT, B.ADDRESS
   FROM <TABLE A> A
    JOIN <TABLE B> B
       ON B.NAME = A.NAME
 ORDER BY A.GROUP, A.NAME, A.ACCOUNT

Then in Perl, you are going to take these results and do your report writing.
 - Keep track of the last Group and Name values
 - Every time the Group changes, spit out a Group Header
 - Every record that comes in with the same Name, add the Account and Address fields to the output record string
 - Every time the Name changes, spit out the output record string, clear it, and drop in the new Name, Group, Account, and Address field.
 - When you run our of records, spit out the output record string

That ought to do it for you with no psychedelic SQL and no stored procedures.

Regards,
Bill
0
 

Author Closing Comment

by:saibsk
ID: 31410232
Thanks. Works great.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Many time we need to work with multiple files all together. If its windows system then we can use some GUI based editor to accomplish our task. But what if you are on putty or have only CLI(Command Line Interface) as an option to  edit your files. I…
I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

739 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