Re: PIVOT tables

neyoisles@aol.com wrote: > > A pivot table is just a crosstab query. Lets say I have a couple of > tables. One stores information about Employees and the other their sales > and the last one shows product information. I want to create a report that > will show me how much each employee sells of a particular item. In access > you have the ability to create these crosstab queries that look something > like this: > > > Product Name Callahan Leverling Suyama > Alice Mutton $312.00 > Chartreuse verte $86.40 > $94.40 > Filo Mix $75.60 > Gnocchi di nonna Alice $1,094.40 > > If you want to do a similar query in SQL Server, you can use the PIVOT > statement in your SQL. I was just wondering if INFORMIX has this > capability. Alden, from what little I can gather about your application, you are not using the relational database in a relational way. I think you need to use a many-to-many relation to associate products with salepeople. This is resolved by a an "intersect" table. Each row of an intersect table contains: 1. A foreign key referencing [the primary key of] a salesperson 2. A foreign key referencing [the primary key of] a product 3. (theoretically optional) Other useful information about the sale - like the $ amount of sales of that product by that salesperson. This way, to see what a salesperson has sold, you query the intersect table by the salesperson's primary key, getting a list of all that person has sold. To find out who is selling a particular product (and how much) you would query by the products primary key. Hey, this stuff is a 3-day class at Informix - Relational Database Design. BTW, once you have the many-to-many relation established & resolved, you can create views & stored procedures that [I think] will simulate the pivot table you seek. Good luck. -- Jake Salomon