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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
