PIVOT tables
neyo...@aol.com
Hi all,
We have some crosstab queries that we had created in access for access
tables. now that we are migrating our tables to informix, we need to have
a way to recreate these crosstab queries using informix tables. I
understand that the PIVOT command (in SQL) is used most often using othere
database servers. Does anybody know if there is a similar command for
Infomix that would allow us to create these PIVOT tables?? We have been
unable to do this so far.
HELP!!
Thanks to all those that respond!!
Alden
Mik Hobbs
I've been using Informix for 7 years and have never heard of pivot
tables. What exactly are you trying to do ? It sounds like a many to
many relationship that you're trying to handle ?
How about posting a description of the tables and the SQL that you're
trying to run.
Mike.
In article <19961202191...@ladder01.news.aol.com>,
neyo...@aol.com writes
--
Mik Hobbs
Nils Myklebust
neyo...@aol.com wrote:
:Hi all,
:We have some crosstab queries that we had created in access for access
:tables. now that we are migrating our tables to informix, we need to have
:a way to recreate these crosstab queries using informix tables. I
:understand that the PIVOT command (in SQL) is used most often using othere
:database servers. Does anybody know if there is a similar command for
:Infomix that would allow us to create these PIVOT tables?? We have been
:unable to do this so far.
There is no pivot table command in the standard Informix engines
(sadly so).
You have several options though:
1. It may work to connect the tables back into MS Access and do the
pivot on them there.
2. If 1. doesn't work you might import the appropriate data into MS
Access. Then the pivot will obviously work, but it may be to
cumbersom.
3. You may look into the Informix MetaCube products that have all
kinds of options to do what you want (and much more than simple
pivot). This isn't inexpensive, but very powerfull.
4. You might write an application in Visual Basic or another MS
Windows based language. Then you can buy ready made controls that can
do the pivot function for you locally.
5. You might write a pivot table function yourself. That isn't easy
(if you want a general one at least), but possible.
6. May be there is enough functionality in the new Universal Server to
do this, or at least make it simpler. In any case the MetaCube product
will be available as a datablade for this server and give you this
option.
:HELP!!
:Thanks to all those that respond!!
:Alden
Nils.My...@idg.no
NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway
My opinions are those of my company
The Informix FAQ is at http://www.iiug.org
neyo...@aol.com
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.
Thanks.
Alden
Josef Dufek
Mik Hobbs <B...@bbt-ltd.demon.co.uk> wrote in article
<tN5ACOA2...@bbt-ltd.demon.co.uk>...
> I've been using Informix for 7 years and have never heard of pivot
> tables. What exactly are you trying to do ? It sounds like a many to
> many relationship that you're trying to handle ?
>
> How about posting a description of the tables and the SQL that you're
> trying to run.
>
>
> Mike.
>
> In article <19961202191...@ladder01.news.aol.com>,
> neyo...@aol.com writes
> >
> >We have some crosstab queries that we had created in access for access
> >tables. now that we are migrating our tables to informix, we need to
have
> >a way to recreate these crosstab queries using informix tables. I
> >understand that the PIVOT command (in SQL) is used most often using
othere
> >database servers. Does anybody know if there is a similar command for
> >Infomix that would allow us to create these PIVOT tables?? We have been
> >unable to do this so far.
> >
> >
> >Thanks to all those that respond!!
> >
> >Alden
>
> Mik Hobbs
>
A PIVOT table is "Microsoft term" (from Excel and SQL server).
From the Excel help:
A pivot table is an interactive worksheet table you use to summarize and
analyze data from an existing list or table
A pivot table is somethink like a crosstab. Some RDBMS support pivot tables
in their SQL.
--
HTH,
Pepa
Josef Dufek
ISS s.r.o., Benesova 2/4, 602 00 Brno, Czech Republic
vo...@issbrno.anet.cz
Josef Dufek
Nils Myklebust <Nils.My...@idg.no> wrote in article
<58471m$8...@nntp.idgonline.no>...
> neyo...@aol.com wrote:
>
> :Hi all,
>
> :We have some crosstab queries that we had created in access for access
> :tables. now that we are migrating our tables to informix, we need to
have
> :a way to recreate these crosstab queries using informix tables. I
> :understand that the PIVOT command (in SQL) is used most often using
othere
> :database servers. Does anybody know if there is a similar command for
> :Infomix that would allow us to create these PIVOT tables?? We have been
> :unable to do this so far.
>
> There is no pivot table command in the standard Informix engines
> (sadly so).
> You have several options though:
> 1. It may work to connect the tables back into MS Access and do the
> pivot on them there.
> 2. If 1. doesn't work you might import the appropriate data into MS
> Access. Then the pivot will obviously work, but it may be to
> cumbersom.
> 3. You may look into the Informix MetaCube products that have all
> kinds of options to do what you want (and much more than simple
> pivot). This isn't inexpensive, but very powerfull.
> 4. You might write an application in Visual Basic or another MS
> Windows based language. Then you can buy ready made controls that can
> do the pivot function for you locally.
> 5. You might write a pivot table function yourself. That isn't easy
> (if you want a general one at least), but possible.
> 6. May be there is enough functionality in the new Universal Server to
> do this, or at least make it simpler. In any case the MetaCube product
> will be available as a datablade for this server and give you this
> option.
>
> :HELP!!
>
> :Thanks to all those that respond!!
>
> :Alden
>
> Nils.My...@idg.no
> NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway
> My opinions are those of my company
> The Informix FAQ is at http://www.iiug.org
>
>
If is your "crosstab query" static, you can write SP (we are using this
for PB clients). It works fine.
You can use some EIS product (Media is very good).
I saw an MS EXCEL extension for pivot tables but i cannot remember
who is selling it (Excel was connect through the ODBC).
Jake Salomon
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