Zapytanie CrossTab (Pivot), Z tabela laczaca Opcje

[Adi32]

(polskie znaki mi wywalilo na ubuntu)

Czesc,

Mam problem z zapytaniem typu crosstab (to w sumie 5 zapytan);

Na wstepie napisze ze te rozwiazania u mnie nie zdaja egzaminu:
http://stackoverflow.com/questions/1238277...-crosstab-query
http://stackoverflow.com/questions/1599709...b-view-in-mysql

Dostaje syntax "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 'FROM csre_profile LEFT JOIN csre_profile_data ON csre_profile.csre_profile_id"
ktory nie ma sensu.

Testowe zapytanie ktore proboje wywolac wyglada tak: (jest poprzeinaczane, robilem wszystko zeby najpierw SQL cokolwiek mi zwrocil)

[SQL] pobierz, plaintext 
SET @sql = NULL;
 
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      ' GROUP_CONCAT(CASE question_normalized when ', question_normalized , ' then question_value else NULL END) AS `',
      question_normalized, '`'
    )
  ) INTO @sql
FROM csre_profile LEFT JOIN csre_profile_data ON csre_profile.csre_profile_id = csre_profile_data.csre_profile_fk 
LEFT JOIN csre_question ON csre_question_id=csre_profile_data.csre_question_fk;
 
SET @sql =
                CONCAT('SELECT csre_profile_id, name, email, ', @sql, '
                  FROM csre_profile LEFT JOIN csre_profile_data ON csre_profile.csre_profile_id = csre_profile_data.csre_profile_fk
                  GROUP BY csre_profile_id LEFT JOIN csre_question ON csre_question_id=csre_profile_data.csre_question_fk');
 
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
[SQL] pobierz, plaintext 

Dane:
3 tabele.

csre_profile: csre_profile_id | name | surname .... === tu niejasnosci nie ma
csre_profile_data: csre_profile_data_id | csre_profile_fk | question_fk | question_value === csre_profile_fk to klucz z tabeli glownej, question_fk to id z tabeli ze zbiorem wartosci (ulica, plec itp), question_value to odpowiedz na pytanie (np: kobieta)
csre_question: csre_questio_id | question_name | question_normalized === question_normalized to pytanie znormalizowane bez pl znakow i spacji.

Chcialbym uzyskac swoim zapytaniem taki efekt:

id | name | gender | avatar .....
1 Zoska Kobieta http://costam

Gdzie kolumny gender i avatar sa wartosciami z tabeli csre_profile_data.

[Adi32]

Dzieki Panowie.

Zaskoczylo i dziala pieknie. Jak beda problemy z indeksowaniem to zrobie jak mowi Nospor.

Zapytanie koncowe wyglada tak:

[SQL] pobierz, plaintext 
SET @sql = NULL;
 
SET SESSION group_concat_max_len = 1000000;
 
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      ' (CASE question_normalized when "', question_normalized , '" then question_value else NULL END) AS `',
      question_normalized, '`'
    )
  ) INTO @sql
FROM csre_profile LEFT JOIN csre_profile_data ON csre_profile.csre_profile_id = csre_profile_data.csre_profile_fk 
 LEFT JOIN csre_question ON csre_question_id=csre_profile_data.csre_question_fk;
 
SET @sql =
                CONCAT('SELECT csre_profile_id, name, email, ', @sql, '
                  FROM csre_profile LEFT JOIN csre_profile_data ON csre_profile.csre_profile_id = csre_profile_data.csre_profile_fk
                  LEFT JOIN csre_question ON csre_question_id=csre_profile_data.csre_question_fk');
 
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
[SQL] pobierz, plaintext 

Jak to jest z poziomu PHP to zapytanie nie dziala?

[SQL] pobierz, plaintext 
An exception occurred while executing 'PREPARE stmt FROM @sql':
 
SQLSTATE[42000]: Syntax error OR access violation: 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
			csre_profile_id, name, email, GROUP_CONCAT(CASE question_normalized wh' at line 3
[SQL] pobierz, plaintext 

[PHP] pobierz, plaintext 
        $em->getConnection()->beginTransaction();
 
        $query = $em->getConnection()->query("SET @sql = NULL");
        $query->execute();
 
        $query = $em->getConnection()->query("SET SESSION group_concat_max_len = 1000000");
        $query->execute();
 
        $query = $em->getConnection()->query("
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'GROUP_CONCAT(CASE question_normalized when \', question_normalized , \' then question_value else NULL END) AS ',
      question_normalized
    )
  ) INTO @sql
FROM csre_profile_data LEFT JOIN csre_profile ON csre_profile.csre_profile_id = csre_profile_data.csre_profile_fk
 LEFT JOIN csre_question ON csre_question_id=csre_profile_data.csre_question_fk
");
        $query->execute();
 
        $query = $em->getConnection()->query("
SET @sql = CONCAT('
    SELECT
			csre_profile_id, name, email, ', @sql, '
    FROM
	 		csre_profile LEFT JOIN csre_profile_data ON csre_profile.csre_profile_id = csre_profile_data.csre_profile_fk
                      LEFT JOIN csre_question ON csre_question_id=csre_profile_data.csre_question_fk
	 GROUP BY csre_profile_id ORDER BY csre_profile_id DESC')
        ");
        $query->execute();
 
        $query = $em->getConnection()->query("PREPARE stmt FROM @sql");
        $query->execute();
 
        $query = $em->getConnection()->query("EXECUTE stmt");
        $query->execute();
 
        $query = $em->getConnection()->query("DEALLOCATE PREPARE stmt");
        $query->execute();
 
        $em->commit();
        die(print_r($query->fetchAll()));
[PHP] pobierz, plaintext