Shawn -
Maybe terminology here...but I think of a "crosstab" query as one which yields sums or averages or
some such tabulation.
What I need to do is just "pivot" from rows to columns.
The way I've come up with is this:
ORIGINAL TABLE:
columns = id date datetype
there are about 20 datetypes (eg. entered_date, modified_date, application_date....)
there can be up to 20 rows for each ID value
DESIRED TABLE: columns = id entered_date modified_date application_date .... so there is just one row for each ID value
SOLUTION: create temporary tables for each of the date types columns for TEMP1 = id entered_date columns for TEMP2 = id modified_date columns for TEMP3 = id application_date .... so on for all date types
then, do an insert into the DESIRED TABLE for all columns, as select ( ... join all 20 tables on the ID value )
Since there are over 23000 ID values, and over 20 date types, this join on all 20 TEMP tables will be an overnight batch process, which is fine for the users - this DESIRED TABLE is for reporting somewhat after the fact, and one day "out-of-synch" is fine.
Any other suggestions?
Donna
[EMAIL PROTECTED] wrote:
I still see what you want as a crosstab query. The only difference, as you say very well, is that you want to pivot on the "date type" values and not the ID values.
The only other thing you need to decide in order to make a crosstab report is "what information goes in the position for the row ID=x and column datetype=y". Do you want to see the SUM of some value for each of the rows with that ID value and Date type? Or the average or maximum or minimum or the standard deviation for the set. I am asking you how do you want to calculate each of the values of x, y, z, and w as you listed them in your example output?
It's the same pattern as the other crosstab queries (aka pivot tables) but you have to tell me which column you want to calculate values from and which calculation to use before I can give you an example using your data.
Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Donna Hinshaw <[EMAIL PROTECTED]> wrote on 08/20/2004 03:13:16 PM:
Hi - I've read the threads about converting rows of data into columns, but those threads assume the number of distinct rows is very limited (say 7 for days of week).
Instead, I have a table like this:
id date date type 1 ... a 1 ... b .... 2 .... a 2 .... d
Where the number of distinct id values is in the 100,000 range but the distinct date types are limited to about 20.
I want to get a table (not a view) like this:
id date a date b date d ...... 1 x y null 2 z null w (based on the values in the first table above)
So...I can't think how to do this. Help would be appreciated.
TIA. dmh
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
