List:General Discussion« Previous MessageNext Message »
From:维斯 苏 Date:August 17 2005 7:05am
Subject:re:how to arrange my table in another direction
View as plain text  
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
1        firstname  Rogier
1        lastname   Marat
2        firstname  Jean
2        lastname   Smith
and you wish create this resultset: 
user_id  firstname  lastname
1        Rogier     Marat
2        Jean       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邮箱-中国第一绝无垃圾邮件骚扰超大邮箱 
Thread
how to arrange my table in another direction维斯 苏16 Aug
  • Re: how to arrange my table in another directionGleb Paharenko16 Aug
  • Re: how to arrange my table in another directionGleb Paharenko16 Aug
    • Re: how to arrange my table in another direction维斯 苏17 Aug
  • Re: how to arrange my table in another directionPeter Brawley17 Aug
re:how to arrange my table in another direction维斯 苏17 Aug
  • Re: how to arrange my table in another directionPeter Brawley17 Aug