hi there this might be an extreme solution.. but i thought you could come up with just one query instead of 20 temp tables.. if you dont mind to write it :)
select id, max(if(datetype='entered_date',date,'')) entered_date, max(if(datetype='modified_date',date,'')) modified_date, max(if(datetype='application_date',date,'')) application_date, . . . from yourtable group by id order by id HTH Leo On Wed, 25 Aug 2004 16:16:05 -0700, Donna Hinshaw <[EMAIL PROTECTED]> wrote: > 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] > >> > >> > >> > > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
