Rotating a table?

I need to turn a set of rows into a single row of multiple columns. I can use a temp table if necessary. Basically, I have a query like this: select colname from syscolumns where tabid=875 and coltype=41; I want a list of rows returned from this. Maybe all I'm looking for is the data from these rows to be select into that temp table, but its got to be on one row. I've struggled with this in the past, and had little luck, tried usign nested self joins (a real mess...) If anyone has any ideas here, I'd appreciate them. Thanks -- David Buttrick http://www.sportingnews.com WEB Database Specialist The Sporting News buttrick@sportingnews.com S E E A D I F F E R E N T G A M E

You could always use a stored procedure that implements a FOREACH loop to create a single row from your multiple rows. It requires that you know how many columns you will need up front and its not elegant but should do the trick. heres a quick example (untested) define v_colanme varchar(18); define i_colnum integer; let i_colnum = 1; foreach select colname into v_colname from syscolumns where tabid=875 and coltype=41 if i_colnum = 1 then update temp_table set col1 = v_colname; end if; if i_colnum = 2 then update temp_table set col2 = v_colname; end if; -- repeat if..end if as necessary let i_column = i_column + 1; end foreach Frank Oelschlager ECOlogic Corporation David Buttrick wrote in message <36BB6055.1F4597FA@sportingnews.com>... >I need to turn a set of rows into a single row of multiple columns. > >I can use a temp table if necessary. > >Basically, I have a query like this: > >select colname >from syscolumns where tabid=875 and coltype=41;> >I want a list of rows returned from this. Maybe all I'm looking for is >the data from these rows to be select into that temp table, but its got >to be on one row. > >I've struggled with this in the past, and had little luck, tried usign >nested self joins (a real mess...) > >If anyone has any ideas here, I'd appreciate them. >Thanks >-- >David Buttrick http://www.sportingnews.com >WEB Database Specialist >The Sporting News buttrick@sportingnews.com > S E E A D I F F E R E N T G A M E