|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!
|
|
#1
April 30th, 2008, 07:36 AM
|
|||
|
|||
|
Confusing SQL problem
Hello,
I have a fairly confusing SQL question. I'll explain it as best I can. I have a table containing a lot of data regarding customers and items they've purchased. A CustomerID can (and does) appear multiple times, as do the ProductIDs. What I'd like to do is run an SQL which will give me results in the following format: CustomerID ProductID1 ProductID2 ProductID3 ProductID4 ProductID5 ProductID6 Note that each product/customer relationship have one record each, so the SQL is essentially selecting the distinct customerIDs and grouping them, then finding each distinct productID that customer is linked to. Is this possible? So the result set has just one record for each CustomerID? Any help would be fantastic. Thanks, Simon
|
|
#2
April 30th, 2008, 12:06 PM
|
||||
|
||||
|
Why dont' you show us the table structure and tell us which database are you using?
__________________
My blog about OpenSource Databases PDF tutorials about OSS databases, DBMonster ... Please contribute to Open Source Development, fill bug reports!!! Developer Shed eSupport Commented my.ini/my.cnf (PLEASE ADD YOUR OWN CONFIG TRICK) An introduction to database normalization Natural or Surrogate key Custom ordering for your results Correlated and uncorrelated subqueries Don't turn your outer joins into inner joins
|
|
#3
April 30th, 2008, 03:51 PM
|
|||
|
|||
|
The database is NexusDB. I don't think it's particularly common, but t accept most normal SQL scripts.
The table structure is as follows: Code:
Key CustomerID ProductID 1 4221 12876 2 4221 98365 3 4222 67787 4 4221 12876 5 4223 12876 6 4221 67787 And I'd like the results to show like this: Code:
CustomerID ProductID1 ProductID2 ProductID3 4221 12876 98365 67787 4222 67787 4223 12876
|
|
#4
April 30th, 2008, 04:58 PM
|
|||
|
|||
|
Quote:
Shame you aren't using MySQL as the group_concat function will do that, but don't think it is available in any other database. If you are outputting using PHP you can emulate it as described in this article. http://www.outshine.com/blog/2007/0...ncat-in-php.php
|
|
#5
May 1st, 2008, 04:53 AM
|
||||
|
||||
|
Quote:
Note that group_concat(), or an equivalent function, is available in many databases, as an example Firebird 2.1 and Sybase support LIST() which works basically the same and PostgreSQL has it's own group_concat() as part of a MySQL compatibility package.
|
|
#6
May 1st, 2008, 08:51 PM
|
|||
|
|||
|
Quote:
I don't suppose you'd know if Nexus has such a function? I looked in the Manual & Reference guide, but couldn't see a mention of it. As for MySQL, I've not had a great deal of experience with it, but I know I can install an ODBC driver for Nexus, to make it accessible from another DB. How would I go about having MySQL connect to the Nexus DB? Is it fairly straightforward or do I have to run scripts etc? (I'm a big fan of GUIs, not so much CLIs).. any help is much appreciated, sorry for asking such newbie questions.
|
|
| Viewing: Dev Shed Forums > Databases > Database Management > Confusing SQL problem |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
