Partition by (weekno, empno) - we want to repeat the creation of 7 hours-cells (or really 7 new records) for every empno and every weeknoĭimension by ('base' day) - with 'base' day we make up a new dimension (a new column in the spreadsheet) we need it to create new rows by assigning cell values using positional notation with new values for this dimension note that the original row in the resultset returned by the from-clause will be extended with an extra value ' base' and alias day. The next query against the employee_week_records table and using a MODEL clause returns the transactional format we are looking for: select weekno We would get something like: WEEKNO EMPNO DAY HOURS So instead of: WEEKNO EMPNO MON TUE WED THU FRI SAT SUN For such operations and reports it would be much better if the data were in a transactional format, with one record per day for each week and employee. This is not an easy format for example to perform analysis on total hour-count per week or per employee. Straight querying against this table will give us records with 9 columns. ( weekno, empno, mon, tue, wed, thu, fri, sat, sun) We have a table employee_week_record that contains timesheets per employee per week: create table employee_week_recordsĪ few records are inserted into this table: insert into employee_week_records Let’s take a look at the following example: Timesheets The SQL MODEL clause, introduced in Oracle 10g Release 1, gives new ways to approach pivoting, both merging and splitting records. The new way to pivot records – merging and splitting using the 10g SQL MODEL This is not very efficient and requires you to duplicate entire SQL statements which is far from ideal. You can ususally make use of a UNION for splitting records. This solution requires SQL that is somewhat difficult to read, understand and maintain: the purpose of the aggregation to condense several rows into one is not immediately clear. For an example, see: Pivot Query on AskTom. This SQL trick seems to be invented by Tom Kyte (see his Expert One-on-One Oracle book) – and can now be seen in many articles. You can “trick” merging records in SQL using in-line views and an aggregation statement (this works in early 8.0 versions of the database). And although Table Functions can be made quite efficient – they even allow parallel execution – they still can probably not compete with pure SQL based solutions. For a simple (!) query, that may be a lot of trouble. Table Functions require you to create an object type (most of the times), a nested table type and a PL/SQL Function. See for example: Turning On Pivot Tables By Jonathan Gennick (Oracle Magazine 2002). Pivoting (both merging and splitting) can easily be implemented using Table Functions. You can also use pivoting to get a nicer, more compact result from a SQL query without procedural logic. The inverse operation is also sometimes required. Usual examples include transactional records such as a list of daily transactions that need to be turned into a record structure, for example a weekly record. Pivoting records means changing their format and typically either splitting or merging records. With those in place, use whatever language you're using to implement business logic in MySQL and implement this pseudo-code: for 1 to BOOK. make the VOUCHER_NUMBER column on the new VOUCHER table in MySQL to be an auto-increment type so you can eliminate the whole start number / units mess.add a book_id column to the VOUCHER table and subsequent foreign key to the BOOK table.If you're taking the time to port this from Oracle to MySQL, seriously give some thought to fixing the data model. Really, this is a symptom of an awful underlying data model: keeping track of the "start number" and "units" in a table - it's like a C array gone bad in a database. a kludge that's giving you incrementing numbers starting at the value of BOOKS.START_NUMBER those then seem to be used as primary keys in the VOUCHER table. The main weirdness going on here is the SELECT ROWNUM FROM DUAL CONNECT BY.