Maison >base de données >tutoriel mysql >Comment transposer efficacement des colonnes et des lignes en SQL ?

Comment transposer efficacement des colonnes et des lignes en SQL ?

Mary-Kate Olsen
Mary-Kate Olsenoriginal
2025-01-23 11:06:09259parcourir

How to Efficiently Transpose Columns and Rows in SQL?

Un moyen simple de convertir des lignes et des colonnes SQL

Bien que la fonction PIVOT de SQL semble adaptée à la conversion colonne-colonne, sa complexité peut être prohibitive. Si vous souhaitez un moyen plus simple d'y parvenir, envisagez les alternatives suivantes :

Utilisez UNION ALL, les fonctions d'agrégation et les instructions CASE

Cette méthode utilise UNION ALL pour développer les données, puis utilise des fonctions d'agrégation et des instructions CASE pour pivoter :

<code class="language-sql">SELECT name,
  SUM(CASE WHEN color = 'Red' THEN value ELSE 0 END) AS Red,
  SUM(CASE WHEN color = 'Green' THEN value ELSE 0 END) AS Green,
  SUM(CASE WHEN color = 'Blue' THEN value ELSE 0 END) AS Blue
FROM
(
  SELECT color, Paul AS value, 'Paul' AS name
  FROM yourTable
  UNION ALL
  SELECT color, John AS value, 'John' AS name
  FROM yourTable
  UNION ALL
  SELECT color, Tim AS value, 'Tim' AS name
  FROM yourTable
  UNION ALL
  SELECT color, Eric AS value, 'Eric' AS name
  FROM yourTable
) AS src
GROUP BY name</code>

Déconstruction statique et perspective

Si vous connaissez la valeur que vous souhaitez convertir, utilisez des valeurs codées en dur pour la déstructuration et le pivotement :

<code class="language-sql">SELECT name, [Red], [Green], [Blue]
FROM
(
  SELECT color, name, value
  FROM yourTable
  UNPIVOT
  (
    value FOR name IN (Paul, John, Tim, Eric)
  ) AS unpiv
) AS src
PIVOT
(
  SUM(value)
  FOR color IN ([Red], [Green], [Blue])
) AS piv</code>

Perspective dynamique

Pour un nombre inconnu de colonnes et de couleurs, utilisez du SQL dynamique pour générer des listes déconstruites et pivotées :

<code class="language-sql">DECLARE @colsUnpivot AS NVARCHAR(MAX),
    @query AS NVARCHAR(MAX),
    @colsPivot AS NVARCHAR(MAX)

SELECT @colsUnpivot = STUFF((SELECT ',' + QUOTENAME(C.name)
         FROM sys.columns AS C
         WHERE C.object_id = OBJECT_ID('yourtable') AND
               C.name  'color'
         FOR XML PATH('')), 1, 1, '')

SELECT @colsPivot = STUFF((SELECT ','
                       + QUOTENAME(color)
                     FROM yourtable AS t
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        , 1, 1, '')

SET @query = 'SELECT name, ' + @colsPivot + '
  FROM (
    SELECT color, name, value
    FROM yourtable
    UNPIVOT
    (
      value FOR name IN (' + @colsUnpivot + ')
    ) AS unpiv
  ) AS src
  PIVOT
  (
    SUM(value)
    FOR color IN (' + @colsPivot + ')
  ) AS piv'

EXEC(@query)</code>

Les trois méthodes produisent les résultats suivants :

NAME RED GREEN BLUE
Eric 3 5 1
John 5 4 2
Paul 1 8 2
Tim 1 3 9

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!

Déclaration:
Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn