Re: how to arrange my table in another direction
but,the query cannot run on my mysql 4.0.23 See http://dev.mysql.com/doc/mysql/en/rewriting-subqueries.html for how to get round the unavailability of subqueries before version 4.1. PB - 维斯 苏 wrote: thank you but after i read it, I try the second example Pivot table using a JOIN Problem: You have table user_class(user_id int, class_id char(20), class_value char(20)) with these rows: user_id class_id class_value 1firstname Rogier 1lastname Marat 2firstname Jean 2lastname Smith and you wish create this resultset: user_id firstname lastname 1Rogier Marat 2Jean Smith This query accomplishes the required pivot table via an INNER JOIN: SELECT t1.user_ID, class_value AS firstname, t2.lastname FROM user_class AS t1 INNER JOIN ( SELECT user_ID, class_value AS lastname FROM user_class WHERE class_id='lastname' ) AS t2 ON t1.user_ID=t2.user_ID AND t1.class_id='firstname' but,the query cannot run on my mysql 4.0.23 it returns: SQL 查询 : SELECT t1.user_ID, class_value AS firstname, t2.lastname FROM user_class AS t1 INNER JOIN ( SELECT user_ID, class_value AS lastname FROM user_class WHERE class_id = 'lastname' ) AS t2 ON t1.user_ID = t2.user_ID AND t1.class_id = 'firstname' LIMIT 0 , 30 MySQL 返回: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT user_ID, class_value AS lastname FROM user_class WHERE c How can i fix it? and thank you for your help. - DO YOU YAHOO!? 雅虎免费G邮箱－中国第一绝无垃圾邮件骚扰超大邮箱 No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.10/73 - Release Date: 8/15/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.11/74 - Release Date: 8/17/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to arrange my table in another direction
Hello. Your question isn't clear enough for me. Do you want that values in 'type' column of the first table to become column names in the second table? Or do you want just to see results in a row? $$ $ [EMAIL PROTECTED] wrote: [-- text/plain, encoding 8bit, charset: gb2312, 17 lines --] My table is: type price car1000 bike 100 I want the result: car bike 1000100 I don't know how to get that result. - DO YOU YAHOO!? $$$2G$pop3$$ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to arrange my table in another direction
Hello. I want the values in the 'type' column of the first table to be the column name in the second table. Please, answer next time to the list as well. In my opinion, it is easier to prepare the create statement on the client side than on the server. And does this table have any sense, considering the relational model? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to arrange my table in another direction
Hello. Please, answer next time to the list as well. In my opinion, it is easier to prepare the create statement on the client side than on the server. And does this table have any sense, considering the relational model? Sorry,I think you misunderstand my purpose,table2 is my query result,in fact my trouble is id customer goods1 price1 goods2 price2 goods3 price3 1 miketv 100 car 100 bike 20 2 jin pc 200psp 200 x360 299 i can't change the table,so I want a query,after the query,the value in goods1 goods2 become the column name,then their value are price1 and price2,i try many methods,but i can't get the result - DO YOU YAHOO!? 雅虎免费G邮箱－No.1的防毒防垃圾超大邮箱
Re: how to arrange my table in another direction
My table is: type price car 1000 bike 100 I want the result: car bike 1000 100 You seem to want to turn rows into columns. That's often called a "pivot table". There's a step-by-step for pivot tables at http://www.artfulsoftware.com/queries.php#24. There're other pivot table examples on the same page. PB No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.10/73 - Release Date: 8/15/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
