Pivots, if understand what you mean, some can be done.

Eg. If the desired data is simple pivot such as a straight transform
with static columns

For instance hours normalised data (three columns : employee, date,
hours worked) to non normalised, employee, hours worked,
day1,day2,day3,day4
So one one column can thus per employee
<COLUMN>
Hours Mon
Hours Tue
Hours Wed
Hours thurs
Hours Fri

To one row per employee with many columns

<COLUMN1> <COLUMN2> <COLUMN3>
Hours Mon Hours tue Hours wed

Just join

SELECT SUM((INVL(tdh.hours, 0)) * TVALUE_1 ) AS DAY_1,
SUM((INVL(tdh.hours, 0)) * TVALUE_2) AS DAY_2,
SUM((INVL(tdh.hours, 0)) * TVALUE_3) AS DAY_3,
SUM((INVL(tdh.hours, 0)) AS WEEKLY

FROM datatable tdh
LEFT JOIN TRANSFORM_WEEKDAY TFW ON TFW.DAY_NUMBER = tdh.day_number

Where TRANSFORM_WEEKDAY is table with an id and matrix transform in it.
Csv table:
Daynumber,TVALUE_1,TVALUE_2,TVALUE_3
1,1,0,0
2,0,1,0
3,0,0,1

The is probably a quicker way but this works for me.



-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Martijn Tonies
Sent: 07 August 2008 12:28
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Pivot Table

Hi,

> Hello, is it possible to generate pivot table?

No.

> I considered to create temporary table and insert data (both in stored
> procedure) but unfortunately stored procedure can't use DDL.

Create the table as a permanent table and use it from your procedure,
include some kind of "session ID" so that you know the data is yours.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB,
Oracle & MS SQL Server Upscene Productions http://www.upscene.com My
thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item on the
main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links