SEO

Does anyone know of a good way to dynamically create a pivot or cross-tab in
Sybase. I'm working with a table where both axis are dynamic in nature. It
is basically a connector table that stores the many to many relationship
between two other tables. Any thoughts appreciated.

Clay Cutbush

Clay Cutbush wrote:

> Does anyone know of a good way to dynamically create a pivot or cross-tab
> in Sybase. I'm working with a table where both axis are dynamic in nature.
> It is basically a connector table that stores the many to many
> relationship between two other tables. Any thoughts appreciated.
>
> Clay Cutbush

I use Sybase 11.0.3 on Linux. Newer versions of Sybase may have additional
functionality.

I assume that your goal is to change a table with many rows into a table
with fewer rows, but more columns. You do not know the names of the
additional columns until run time.

For example table2 is created from table1:
Table1:
store_no product sales_amt
1 soap 10.00
2 soap 2.00
3 beer 3.00
1 beer 7.00
1 soap 6.00

Table2:
store_no soap beer
1 16.00 7.00
2 2.00 0.00
3 0.00 3.00

The problem is that Sybase 11.0.3 TSQL will not allow variables to be used
as column names. I get around this limitation by
a. creating table2 with only the store_no column
b. determining the column names (soap, beer) to be added to table2
c. creating a for loop to process each column name. Use echo commands within
the for loop to create a temporary isql script with the correct column
name. Call the temporary script. This temporary script adds a column to
table2 and then populates it with data from table1.

This method is crude, but works. If you are interested, I should be able to
provide an example of a script. As I do not read this newgroup every day,
send me an email (lelson49@worldnet.att.net) and I will post my reply to
this newsgroup.

Larry Elson