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:
> >>
> >>iddate 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:
> >>
> >>iddate 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]
