grouping

[avivit]

I'm not sure it's a "group by" thing at all, but...
I have a table the holds "client" field and "orderName" field.
Sometimes, the same client name was inserted to differrent records. I don't want to prevent it, but I do want to have a query that shows me in one row, all orders concerning each client. like: joe orderName orderName orderName.
(I only want the duplicates records this way, I don't wanna see the rest) Any idea? Thanks.

 

[thumbelina]

try:

SELECT ordername FROM tablename WHERE clientname = joe

if u just want one client at a time if u want all the clients at once then try:

SELECT * FROM tablename GROUP BY client name

hope that helps

 

[avivit]

Thanks, but it is not a valid sql statement.

 

[thumbelina]

ok the group by statment it seems is very complex and i don't really understand it i'm sorry. did u try a net search for a SQL info site?? i'm afraid that's all i can suggest, good luck.

 

[THoey]

If you are trying to get records like this:

CLIENT      ORDERNAME
JOE         Order1
JOE         Order2
BOB         Order1
BOB         Order2

Use the following:

SELECT client, ordername
FROM MyTable
ORDER BY client, ordername;

The GROUP BY clause is use for creating totals, etc...

Hope this helps... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[avivit]

Thanks thumbelina and th3856. Actually that is not what I want. I want to have one row for each client (if it has more than one order) and in the same row have some all orders name.
I think it's something with "crossrab" query type.

 

[MalcolmW]

If this is your data, what do you want to see? This is not entirely clear.
CLIENTID ORDERID
JOE Order1
JOE Order2
JOE Order6
BOB Order3
BOB Order7
MOE Order5 Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[avivit]

MalcolmW, thanks for trying ti help.
Here what I like to see. Sorry for being unclear.

CLIENTID ORDERID ORDERID ORDERID
JOE Order1 Order2 order6
Bob order3 order5

 

[thumbelina]

i think that MalcolmW gave you the right SQL line. What you want to do is a matter of formatting within what ever program you are using the SQL in.

 

[THoey]

You can do this, and I have seen it done on this site, but I can't remember how to do it. Let me see if I can search for it... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[grega]

I don't think this can be done using straight SQL (I could be wrong). A "crosstab" query is slightly different in that you interpret the rows selected in some way (usually with a GROUP BY clause, and use the CASE/DECODE statements to build up the results.

In your example, it's a pure and simple case of formatting the data, i.e. no interpretation is required. My feeling is that because of this, you need some form of report generator to achieve your result.

Greg. (who would be very happy to be proved wrong as he wouldn't mind knowing how to do this himself

 

[avivit]

Yes yes th3856, pls find it out for me.
Not only my access starts to not function properly,
I also have some unsolved things.
I'm eagering to prove Greg he's wrong as a side matter, for I'm sure it's possible.
In the mean time, thanks all..

 

[THoey]

Everything I can find requires a third field to do some sort of grouping on. If you use the keyword search function of this site, look up "crosstab" just in this forum. Several people have asked this question before. Maybe some of their posts can give you an idea. Sorry...

I am thinking Greg was right... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[avivit]

Ok, thanks much, I'll keep searching.

 

[MalcolmW]

This is a situation where you are trying to pound a square peg in a round hole. A dataset for SQL works best when you have defined number of fields, and any number of records. However, in your situation, the number of fields depends on the number of orderid's for a clientid.
If you had a fixed number of orderid's, then you could use the CASE statement to populate the field values with orderids, stuffing nulls into the extra fields for clients that had less the fixed number of orderids.
A simpler approach would be to create a single field that contained all the orderids for a clientid, such as

CLIENTID    OrderID_Count ORDERIDs
JOE         3             Order1, Order2, Order6
Bob         2             Order3, Order5

Getting rid of the single orders would just be a matter of a Having clause like
HAVING Count(OrderID) > 1
This is not what you asked for, but is more easily achievable. Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[MikeJones]

I've had to do a similar tghing in Oracle and the only solution we came up with was to write a PL/SQL function that took the client ID and returned a concatonated string of the orders. It's quite simple if you know PL/SQL and of course means you'd have to be running on Oracle. If your running MS I don't know if it allows you to bolt on user defined functions.

If you want the Oracle Solution post here and I'll post the code.

 

[sagn]

This may be more work than you were thinking of but
you could put it all in one table
as

clientid orderid
joe 2
joe 3
joe 4

etc

then create another table, table2 having N columns
(where N is the width of your desired table)

SELECT TOP 1 COUNT(clientid)
FROM table1
GROUP BY clientid

will give you N-1 desired columns

clientid id1 id2 ... id(N-1)

and copy the fields over. It's like transposing a matrix
I think.


ds

 

[sagn]

Oh.. I think you should include and

'order by count(clientid) desc' in the
above select stmt.


ds

 

[listener22]

first, to get only the records that have 'duplicates'

in your select statement add

having count(*) > 1 before your group by statement


second, in Oracle sql*plus you can define columns, and you can
define something like levels, sorry it's been too long, so that your
report would look like

Joe
order1
order6
order8

Jill
order2
order3

etc.

I don't remember the specifics, but if anyone using Oracle sql
to write reports can fill in the blanks. Jim

oracle, vb