Comment puis-je transposer facilement des colonnes et des lignes dans SQL ?

Implémentez facilement la transposition de colonnes et de lignes SQL

En SQL, il est souvent nécessaire d'échanger des lignes et des colonnes. Bien que PIVOT semble être un outil approprié, il existe des situations où il semble trop complexe. Heureusement, il existe des alternatives plus simples pour réaliser cette conversion.

Utilisez UNION ALL, agrégez les fonctions et les instructions CASE

Si PIVOT semble intimidant, vous pouvez utiliser UNION ALL combiné avec une fonction d'agrégation et une instruction CASE pour reproduire ses fonctionnalités :

<code class="language-sql">select name,
  sum(case when color = 'Red' then value else 0 end) Red,
  sum(case when color = 'Green' then value else 0 end) Green,
  sum(case when color = 'Blue' then value else 0 end) Blue
from
(
  select color, Paul value, 'Paul' name
  from yourTable
  union all
  select color, John value, 'John' name
  from yourTable
  union all
  select color, Tim value, 'Tim' name
  from yourTable
  union all
  select color, Eric value, 'Eric' name
  from yourTable
) src
group by name</code>

UNPIVOT et PIVOT utilisant des valeurs statiques

Si vous connaissez la colonne et la couleur spécifiques que vous souhaitez convertir, vous pouvez les coder en dur pour une exécution statique :

<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)
  ) unpiv
) src
pivot
(
  sum(value)
  for color in ([Red], [Green], [Blue])
) piv</code>

Utilisez UNPIVOT et PIVOT dynamiques pour gérer les valeurs inconnues

Dans les cas où le nombre de colonnes et de couleurs est inconnu, du SQL dynamique peut être utilisé pour générer les listes nécessaires :

<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 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+')
        ) unpiv
      ) src
      pivot
      (
        sum(value)
        for color in ('+@colsPivot+')
      ) piv'

exec(@query)</code>

Peu importe la méthode que vous choisissez, les résultats sont les mêmes :

| NOM | ROUGE | VERT | BLEU |

|Éric | 3 | 5 |
| Jean | 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!