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

Transposition de colonnes et de lignes SQL : un guide pratique

SQL nécessite souvent de transposer les données – en convertissant l'orientation du tableau de verticale (colonnes) à horizontale (lignes) ou inversement. Bien que la commande PIVOT existe, elle peut être fastidieuse. Ce guide explore des alternatives plus simples.

Méthode 1 : déclaration UNION ALL, Aggregate et CASE

Cette approche utilise UNION ALL pour annuler le pivotement, puis une fonction d'agrégation (ici, SUM) et une instruction CASE pour repivoter :

<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>

Méthode 2 : UNPIVOT et PIVOT statiques

Connaître le nombre de colonnes à transformer permet d'avoir une solution statique UNPIVOT et PIVOT :

<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>

Méthode 3 : Pivot dynamique pour les colonnes variables

Lorsqu'il s'agit d'un nombre dynamique de colonnes et de couleurs, le SQL dynamique offre une solution :

<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>

Ces méthodes offrent des approches polyvalentes de transposition de données en SQL, s'adaptant aux divers besoins de manipulation de données.

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!