[Aporte] Link interesante

[quimfv]

No se si ya se habia reportado, pero me ha parecido una interesante colección de ejemplos


http://www.artfulsoftware.com/infotree/queries.php

Un ejemplo... eso casi son referencias cruzadas...

Código pre:

Pivot table without GROUP_CONCAT
 
Data designs often require flexibility in numbers and names of data points per 
instance row: instead of saving all the data points belonging to a key value in 
a single row, you save each data point as a name-value pair in its own row.
 
Thus given 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 a resultset that links first names to last names for each ID...
 
user_id  firstname  lastname
1        Rogier     Marat
2        Jean       Smith
 
the following query accomplishes the required pivot via an INNER SELF-JOIN:
 
SELECT 
  u1.user_ID,
  class_value AS firstname,
  u2.lastname 
FROM user_class AS u1
INNER JOIN (
  SELECT 
    user_ID,
    class_value AS lastname 
  FROM user_class
  WHERE class_id='lastname'
) AS u2 
ON u1.user_ID=u2.user_ID AND u1.class_id='firstname'