Sybase group by

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.
Who is Participating?
grant300Connect With a Mentor Commented:
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...

       ON B.NAME = A.NAME

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.

What results do you want?
saibskAuthor Commented:

Name      Group      Account      Address
John      1      1      NJ
John      1      2      NY
Harry      1      3      CA
Smith      2      4      MO
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

saibskAuthor Commented:
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.
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
saibskAuthor Commented:
Thanks. Works great.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.