Thanks! I didn't remember UNION - that does the trick. On Monday 16 July 2007 15:49, Rolando Edwards wrote: > drop table if exists test.flipdata; > drop view if exists test.RotatedData; > create table test.flipdata (f1 int,f2 char(1),f3 char(1)); > insert into test.flipdata values (1,'a','b'),(2,'c','d'); > create view test.RotatedData as select f1,f2 from test.flipdata union > select f1,f3 from test.flipdata order by 1; select * from test.RotatedData; > > These are the results I got running MySQL 5.0.45 in Windows: > > mysql> drop table if exists test.flipdata; > Query OK, 0 rows affected (0.00 sec) > > mysql> drop view if exists test.RotatedData; > Query OK, 0 rows affected (0.00 sec) > > mysql> create table test.flipdata (f1 int,f2 char(1),f3 char(1)); > Query OK, 0 rows affected (0.06 sec) > > mysql> insert into test.flipdata values (1,'a','b'),(2,'c','d'); > Query OK, 2 rows affected (0.00 sec) > Records: 2 Duplicates: 0 Warnings: 0 > > mysql> create view test.RotatedData as select f1,f2 from test.flipdata > union select f1,f3 from test.flipdata order by 1; Query OK, 0 rows affected > (0.00 sec) > > mysql> select * from test.RotatedData; > +------+------+ > > | f1 | f2 | > > +------+------+ > > | 1 | a | > | 1 | b | > | 2 | c | > | 2 | d | > > +------+------+ > 4 rows in set (0.00 sec) > > ----- Original Message ----- > From: "Rolando Edwards" <[EMAIL PROTECTED]> > To: "Stefan Kuhn" <[EMAIL PROTECTED]> > Cc: "MySQL" <mysql@lists.mysql.com> > Sent: Monday, July 16, 2007 9:44:48 AM (GMT-0500) America/New_York > Subject: Re: Putting two queries in one > > drop table if exists test.flipdata; > create table test.flipdata (f1 int,f2 char(1),f3 char(1)); > insert into test.flipdata values (1,'a','b'),(2,'c','d'); > create view test.RotatedData as select f1,f2 from test.flipdata union > select f1,f3 from test.flipdata order by 1; select * from test.RotatedData; > > ----- Original Message ----- > From: "Stefan Kuhn" <[EMAIL PROTECTED]> > To: "MySQL" <mysql@lists.mysql.com> > Sent: Monday, July 16, 2007 8:58:58 AM (GMT-0500) America/New_York > Subject: Putting two queries in one > > Hi all, > I have to create a view. For this, a table needs to transformed like this: > So the original table is > 1 a b > 2 c d > I want > 1 a > 1 b > 2 c > 2 d > I can do two queries giving me this, but how do I combine them in a view? > If a new table would need to be created, I could do a select into - but in > a view? It would need to be done in one query, if I understood it right. > Anybody an idea? > Thanks, > Stefan > -- > Stefan Kuhn BSc MA > IPB Halle > AG Bioinformatik & Massenspektrometrie > Weinberg 3 > 06120 Halle > http://www.ipb-halle.de http://msbi.bic-gh.de > [EMAIL PROTECTED] +49 (0) 345 5582 1474 Fax.+49 (0) 345 5582 1409 > > -- > 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]
-- Stefan Kuhn BSc MA IPB Halle AG Bioinformatik & Massenspektrometrie Weinberg 3 06120 Halle http://www.ipb-halle.de http://msbi.bic-gh.de [EMAIL PROTECTED] +49 (0) 345 5582 1474 Fax.+49 (0) 345 5582 1409 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
