Comment transposer facilement des colonnes et des lignes en SQL ?

Un moyen simple de convertir des lignes et des colonnes SQL

En SQL, il est courant de convertir les données de lignes et de colonnes pour obtenir un format de données plus pratique. Bien que la fonction PIVOT semble compliquée, il existe en réalité des alternatives plus simples.

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

Si vous ne pouvez pas utiliser la fonction PIVOT, vous pouvez utiliser une combinaison de UNION ALL, des fonctions d'agrégation et des instructions CASE :

<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éthodes statiques UNPIVOT et PIVOT

Si la valeur de la colonne à convertir est connue, vous pouvez utiliser les fonctions 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 PIVOT dynamique

Pour un nombre inconnu de colonnes et de couleurs, du SQL dynamique peut être utilisé :

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

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!