Un moyen simple de transposer des colonnes et des lignes en SQL ?

Question

Comment échanger simplement des colonnes avec des lignes en SQL ? Existe-t-il une commande simple pour transposer ?

ie tourner ce résultat :

        Paul  | John  | Tim  |  Eric
Red     1       5       1       3
Green   8       4       3       5
Blue    2       2       9       1

dans ceci :

        Red  | Green | Blue
Paul    1       8       2
John    5       4       2
Tim     1       3       9
Eric    3       5       1

PIVOT semble trop complexe pour ce scénario.

Answer 1

Il existe plusieurs façons de transformer ces données. Dans votre message initial, vous avez déclaré que PIVOT semble trop complexe pour ce scénario, mais elle peut être appliquée très facilement en utilisant à la fois la fonction UNPIVOT y PIVOT dans SQL Server.

Toutefois, si vous n'avez pas accès à ces fonctions, il est possible de les reproduire en utilisant les éléments suivants UNION ALL a UNPIVOT et ensuite une fonction agrégée avec un CASE déclaration à PIVOT :

Créer une table :

CREATE TABLE yourTable([color] varchar(5), [Paul] int, [John] int, [Tim] int, [Eric] int);

INSERT INTO yourTable
    ([color], [Paul], [John], [Tim], [Eric])
VALUES
    ('Red', 1, 5, 1, 3),
    ('Green', 8, 4, 3, 5),
    ('Blue', 2, 2, 9, 1);

Union All, Aggregate et CASE Version :

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

Ver Bricolage SQL avec démo

El UNION ALL effectue le UNPIVOT des données en transformant les colonnes Paul, John, Tim, Eric en rangs séparés. Ensuite, vous appliquez la fonction d'agrégation sum() avec le case pour obtenir les nouvelles colonnes pour chaque color .

Version statique de Unpivot et Pivot :

Les deux UNPIVOT y PIVOT dans le serveur SQL rendent cette transformation beaucoup plus facile. Si vous connaissez toutes les valeurs que vous voulez transformer, vous pouvez les coder en dur dans une version statique pour obtenir le résultat :

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

Ver Bricolage SQL avec démo

La requête interne avec le UNPIVOT remplit la même fonction que le UNION ALL . Il prend la liste des colonnes et la transforme en rangées, les PIVOT effectue ensuite la transformation finale en colonnes.

Version Pivot Dynamique :

Si vous avez un nombre inconnu de colonnes ( Paul, John, Tim, Eric dans votre exemple) et ensuite un nombre inconnu de couleurs à transformer, vous pouvez utiliser le sql dynamique pour générer la liste à UNPIVOT et ensuite PIVOT :

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)

Ver Bricolage SQL avec démo

La version dynamique interroge à la fois yourtable et ensuite le sys.columns pour générer la liste des éléments à UNPIVOT y PIVOT . Elle est ensuite ajoutée à une chaîne de requête à exécuter. L'avantage de la version dynamique est que si vous avez une liste changeante de colors et/ou names cela générera la liste au moment de l'exécution.

Les trois requêtes produiront le même résultat :

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

Answer 2

Cela nécessite normalement de connaître au préalable TOUS les libellés des colonnes ET des lignes. Comme vous pouvez le voir dans la requête ci-dessous, les étiquettes sont toutes listées dans leur intégralité dans les opérations UNPIVOT et (re)PIVOT.

Configuration des schémas de MS SQL Server 2012 :

create table tbl (
    color varchar(10), Paul int, John int, Tim int, Eric int);
insert tbl select 
    'Red' ,1 ,5 ,1 ,3 union all select
    'Green' ,8 ,4 ,3 ,5 union all select
    'Blue' ,2 ,2 ,9 ,1;

Requête 1 :

select *
from tbl
unpivot (value for name in ([Paul],[John],[Tim],[Eric])) up
pivot (max(value) for color in ([Red],[Green],[Blue])) p

Résultats :

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

Notes supplémentaires :

1. Étant donné le nom d'une table, vous pouvez déterminer tous les noms de colonnes à partir des éléments suivants sys.columns ou pour une astuce XML en utilisant nom-local() .
2. Vous pouvez également établir la liste des couleurs distinctes (ou des valeurs pour une colonne) à l'aide de FOR XML.
3. Les éléments ci-dessus peuvent être combinés dans un batch sql dynamique pour traiter n'importe quelle table.

Answer 3

Je voudrais signaler quelques solutions supplémentaires pour transposer des colonnes et des lignes en SQL.

La première est l'utilisation du CURSEUR. Bien que le consensus général dans la communauté professionnelle soit de rester à l'écart des curseurs du serveur SQL, il y a encore des cas où l'utilisation des curseurs est recommandée. Quoi qu'il en soit, les curseurs nous offrent une autre option pour transposer les lignes en colonnes.

• Expansion verticale

  Tout comme le PIVOT, le curseur a la capacité dynamique d'ajouter des lignes au fur et à mesure que votre ensemble de données s'élargit pour inclure plus de numéros de police.

• Expansion horizontale

  Contrairement au PIVOT, le curseur excelle dans ce domaine car il est capable de s'étendre pour inclure le document nouvellement ajouté, sans altérer le script.

• Ventilation des performances

  La principale limitation de la transposition des lignes en colonnes à l'aide d'un CURSEUR est un inconvénient lié à l'utilisation des curseurs en général - ils ont un coût important en termes de performances. Ceci est dû au fait que le curseur génère une requête séparée pour chaque opération FETCH NEXT.

Une autre solution pour transposer les lignes en colonnes est d'utiliser le XML.

La solution XML de transposition des lignes en colonnes est en fait une version optimale du PIVOT, dans la mesure où elle tient compte de la limitation dynamique des colonnes.

La version XML du script répond à cette limitation en utilisant une combinaison de XML Path, de T-SQL dynamique et de certaines fonctions intégrées (c'est-à-dire STUFF, QUOTENAME).

• Expansion verticale

  Comme pour le PIVOT et le Curseur, les politiques nouvellement ajoutées peuvent être récupérées dans la version XML du script sans modifier le script original.

• Expansion horizontale

  Contrairement au PIVOT, les documents nouvellement ajoutés peuvent être affichés sans modifier le script.

• Ventilation des performances

  En termes d'IO, les statistiques de la version XML du script sont presque similaires à celles du PIVOT - la seule différence est que le XML a un deuxième scan de la table dtTranspose mais cette fois à partir d'une lecture logique - cache de données.

Vous trouverez plus d'informations sur ces solutions (y compris des exemples réels en T-SQL) dans cet article : https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/

Answer 4

Sur cette base solution de Pieds bleus Voici une procédure stockée qui utilise le sql dynamique pour générer la table transposée. Elle exige que tous les champs soient numériques, à l'exception de la colonne transposée (la colonne qui sera l'en-tête de la table résultante) :

/****** Object:  StoredProcedure [dbo].[SQLTranspose]    Script Date: 11/10/2015 7:08:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Paco Zarate
-- Create date: 2015-11-10
-- Description: SQLTranspose dynamically changes a table to show rows as headers. It needs that all the values are numeric except for the field using for     transposing.
-- Parameters: @TableName - Table to transpose
--             @FieldNameTranspose - Column that will be the new headers
-- Usage: exec SQLTranspose <table>, <FieldToTranspose>
-- =============================================
ALTER PROCEDURE [dbo].[SQLTranspose] 
  -- Add the parameters for the stored procedure here
  @TableName NVarchar(MAX) = '', 
  @FieldNameTranspose NVarchar(MAX) = ''
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  DECLARE @colsUnpivot AS NVARCHAR(MAX),
  @query  AS NVARCHAR(MAX),
  @queryPivot  AS NVARCHAR(MAX),
  @colsPivot as  NVARCHAR(MAX),
  @columnToPivot as NVARCHAR(MAX),
  @tableToPivot as NVARCHAR(MAX), 
  @colsResult as xml

  select @tableToPivot = @TableName;
  select @columnToPivot = @FieldNameTranspose

  select @colsUnpivot = stuff((select ','+quotename(C.name)
       from sys.columns as C
       where C.object_id = object_id(@tableToPivot) and
             C.name <> @columnToPivot 
       for xml path('')), 1, 1, '')

  set @queryPivot = 'SELECT @colsResult = (SELECT  '','' 
                    + quotename('+@columnToPivot+')
                  from '+@tableToPivot+' t
                  where '+@columnToPivot+' <> ''''
          FOR XML PATH(''''), TYPE)'

  exec sp_executesql @queryPivot, N'@colsResult xml out', @colsResult out

  select @colsPivot = STUFF(@colsResult.value('.', 'NVARCHAR(MAX)'),1,1,'')

  set @query 
    = 'select name, rowid, '+@colsPivot+'
        from
        (
          select '+@columnToPivot+' , name, value, ROW_NUMBER() over (partition by '+@columnToPivot+' order by '+@columnToPivot+') as rowid
          from '+@tableToPivot+'
          unpivot
          (
            value for name in ('+@colsUnpivot+')
          ) unpiv
        ) src
        pivot
        (
          sum(value)
          for '+@columnToPivot+' in ('+@colsPivot+')
        ) piv
        order by rowid'
  exec(@query)
END

Vous pouvez le tester avec le tableau fourni avec cette commande :

exec SQLTranspose 'yourTable', 'color'

Answer 5

Je fais UnPivot d'abord et de stocker les résultats dans CTE et en utilisant le CTE en Pivot fonctionnement.

Démo

with cte as
(
select 'Paul' as Name, color, Paul as Value 
 from yourTable
 union all
 select 'John' as Name, color, John as Value 
 from yourTable
 union all
 select 'Tim' as Name, color, Tim as Value 
 from yourTable
 union all
 select 'Eric' as Name, color, Eric as Value 
 from yourTable
 )

select Name, [Red], [Green], [Blue]
from
(
select *
from cte
) as src
pivot 
(
  max(Value)
  for color IN ([Red], [Green], [Blue])
) as Dtpivot;