You'd have to search the columns up first to do that. Something like SELECT DISTINCT(MONTH) FROM MYTABLE
Then you could use a rows loop to create your SQL statement <@ROWS> <@ASSIGN NAME='MYSQL' SCOPE='LOCAL' VALUE='@@MYSQL (SUM((IFEQ(month, <COLUMN NAME="MONTH">, COUNT, 0)))) AS MONTH,'> </@ROWS> or something along those lines. You'd end up with a SQL statement that you could stick into your dbms command like Select name, <@LEFT STR=@@local$MySQL NUMCHARS=<@CALC EXPR='<@LENGTH STR=@@local$MySQL>-1'>> from mytable group by Name Or you may be able to do it on the database side with a temp table, depending on your db server. It's a similar problem - you have to know what your columns are to be able to group by them. > -----Original Message----- > From: Dave Machin [mailto:[EMAIL PROTECTED] > Sent: Tuesday, August 19, 2003 10:20 AM > To: [EMAIL PROTECTED] > Subject: Re: Witango-Talk: Array Manipulation Puzzle > > > Hmm, sounds interesting, but isn't that assuming that I know > how many elements are in the second column (month) of the > resultset? And that the query is hard-coded to expect that > result? Is there a way to do it if the number/names of the > elements in the second column is unknown? > > ----- Original Message ----- > From: "Troy Sosamon" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Monday, August 18, 2003 4:33 PM > Subject: RE: Witango-Talk: Array Manipulation Puzzle > > > > Do a direct dbms command using a select and a group by > something like > this: > > > > select name, (sum((ifeq(month, 'jan', count, 0)))) as Jan, > (sum((ifeq(month, > > 'Feb', count, 0)))) as Feb, ...... > > from mytable group by Name > > > > Troy > > > > -----Original Message----- > > From: Dave Machin [mailto:[EMAIL PROTECTED] > > Sent: Monday, August 18, 2003 5:14 PM > > To: [EMAIL PROTECTED] > > Subject: Witango-Talk: Array Manipulation Puzzle > > > > > > I have many queries that return results like this: > > > > Name Month Count > > Fred Jan 50 > > Wilma Jan 32 > > Barney Jan 33 > > Betty Jan 30 > > Fred Feb 60 > > Wilma Feb 42 > > Barney Feb 43 > > Betty Feb 40 > > Fred Mar 40 > > Wilma Mar 22 > > Barney Mar 23 > > Betty Mar 20 > > > > etc... > > > > I often need to convert that into a table or array that looks like > > this: > > > > Name Jan Feb Mar > > Fred 50 60 40 > > Wilma 32 42 22 > > Barney 33 43 23 > > Betty 30 40 20 > > > > What is the simplest/fastest way to do that conversion? > > > > Dave Machin > > > > > > E-Mail. [EMAIL PROTECTED] > > Tel. 805.614.0123 x 30 > > Address: 3130 Skyway Drive #702 > > Santa Maria, CA 93455 > > > > > ______________________________________________________________________ > > __ > > TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf > > > > > ______________________________________________________________________ > > __ > > TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf > > > > > > > ______________________________________________________________ > __________ > TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf > ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf
