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]