[MySql] Trasformazione da verticale ad orizzontale

[nat99]

Originariamente inviato da Enkh
perchè cosa devi fare?

necessità per gestionale... storia lunga.

[Enkh]

Funziona, ma non è elegante. Devi essere sicuro che il numero di livelli massimo sia fisso. L'ho scritta per max 5 livelli come mi avevi chiesto tu.

Se puoi, metti mi piace come da firma

SELECT *
FROM
  (
    SELECT 
      CATEGORIA AS CAT1, 
      '' AS CAT2,
      '' AS CAT3,
      '' AS CAT4,
      '' AS CAT5 
    FROM CATEGORIE 
    WHERE ID_CATEGORIA = 0
    
    UNION ALL
    
    SELECT 
      C1.CATEGORIA AS CAT1,
      C2.CATEGORIA AS CAT2,
      '' AS CAT3,
      '' AS CAT4,
      '' AS CAT5 
    FROM CATEGORIE AS C1
    INNER JOIN CATEGORIE AS C2 ON C1.ID = C2.ID_CATEGORIA 
    WHERE C1.ID_CATEGORIA = 0
    
    UNION ALL
    
    SELECT 
      C1.CATEGORIA AS CAT1,
      C2.CATEGORIA AS CAT2,
      C3.CATEGORIA AS CAT3,
      '' AS CAT4,
      '' AS CAT5 
    FROM CATEGORIE AS C1
    INNER JOIN CATEGORIE AS C2 ON C1.ID = C2.ID_CATEGORIA
    INNER JOIN CATEGORIE AS C3 ON C2.ID = C3.ID_CATEGORIA  
    WHERE C1.ID_CATEGORIA = 0
    
    UNION ALL
    
    SELECT 
      C1.CATEGORIA AS CAT1,
      C2.CATEGORIA AS CAT2,
      C3.CATEGORIA AS CAT3,
      C4.CATEGORIA AS CAT4,
      '' AS CAT5 
    FROM CATEGORIE AS C1
    INNER JOIN CATEGORIE AS C2 ON C1.ID = C2.ID_CATEGORIA
    INNER JOIN CATEGORIE AS C3 ON C2.ID = C3.ID_CATEGORIA
    INNER JOIN CATEGORIE AS C4 ON C3.ID = C4.ID_CATEGORIA  
    WHERE C1.ID_CATEGORIA = 0
    
    UNION ALL
    
    SELECT 
      C1.CATEGORIA AS CAT1,
      C2.CATEGORIA AS CAT2,
      C3.CATEGORIA AS CAT3,
      C4.CATEGORIA AS CAT4,
      C5.CATEGORIA AS CAT5
    FROM CATEGORIE AS C1
    INNER JOIN CATEGORIE AS C2 ON C1.ID = C2.ID_CATEGORIA
    INNER JOIN CATEGORIE AS C3 ON C2.ID = C3.ID_CATEGORIA
    INNER JOIN CATEGORIE AS C4 ON C3.ID = C4.ID_CATEGORIA
    INNER JOIN CATEGORIE AS C5 ON C4.ID = C5.ID_CATEGORIA  
    WHERE C1.ID_CATEGORIA = 0 
  
  ) AS TAB
  
  ORDER BY
    CAT1,
    CAT2,
    CAT3,
    CAT4,
    CAT5

[nat99]

Gentilissimo!
Like messo e ti condivido anche la pagina

Nat