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