Confusing SQL

[scruft]

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

 

[hneal98]

you can do a search on the forum for "Denormalizing a table". I have found several examples in the past on this subject from others who have asked the same question.

 

[r937]

there is no nice ANSI SQL way to do this

you did not mention which database system you're using, so i cannot recommend the forum where you should ask this question

basically you want to search for "crosstab query" in the forum for your database system

r937.com | rudy.ca

 

[simian101]

Here are some great SQL Server emamples.

Other databases may require a cursor.

Simi

 

[simian101]

Oops for got the link...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Simi