Pivoter dynamiquement ses tables

Par Arian Papillon le jeudi 27 septembre 2012, 19:35 - Lien permanent

Les requêtes pivot sont très utilisées pour l'analyse des données. Ces requêtes permettent de présenter les données agrégées sous une forme facilement lisible, en transposant les valeurs uniques contenues dans une colonne en plusieurs colonnes de sortie. En TSQL, il existe l'opérateur PIVOT pour effectuer cette opération. Nous avons essayé de rendre l'opération un peu plus dynamique...

Voyons d'abord quelques exemples de l'opérateur PIVOT

1. Exemple 1

J'ai par exemple la table 'sample1' suivante :

Je souhaiterais obtenir la table pivotée suivante :

La requête PIVOT en TSQL donnerait quelque chose comme ceci :

    SELECT * FROM
    (
        SELECT 'Nombre_Adhesions' AS _NAME_, [Periode] AS pivot_col, [Nombre_Adhesions] FROM [sample1]
    ) AS t 
    PIVOT 
    (
        SUM([Nombre_Adhesions]) FOR pivot_col IN ([_201106],[_201107],[_201108])
    ) as p
 ORDER BY _NAME_

Inconvénient majeur de cette commande, je dois y mentionner la liste des colonnes, et donc savoir quelles périodes sont contenues dans la table !

2. Exemple 2

Prenons un autre exemple : dans cette table 'sample4', j'ai 2 colonnes de valeur (Nombre_Fabrique et Nombre_Vendu), 1 colonne à pivoter (l'année) et 2 colonnes à regrouper (Produit et Couleur).

L'objectif est d'obtenir une table pivotée (sur l'année), regroupée (sur le produit et la couleur) et qui agrège mes deux valeurs (Nombre_Fabrique et Nombre_Vendu)

La requête, un peu trop longue à écrire, qui permet d'obtenir ce résultat est la suivante, elle présente les mêmes inconvénients que celle vue plus haut :

    SELECT Produit,Couleur,_NAME_,coalesce([2010],0) AS [2010],coalesce([2011],0) AS [2011] FROM
    (
        SELECT Produit,Couleur, 'Nombre_Fabrique' AS _NAME_, [Annee] AS pivot_col, [Nombre_Fabrique] FROM [sample4]
    ) AS t 
    PIVOT 
    (
        SUM([Nombre_Fabrique]) FOR pivot_col IN ([2010],[2011])
    ) as p
 
UNION ALL 

    SELECT Produit,Couleur,_NAME_,coalesce([2010],0) AS [2010],coalesce([2011],0) AS [2011] FROM
    (
        SELECT Produit,Couleur, 'Nombre_Vendu' AS _NAME_, [Annee] AS pivot_col, [Nombre_Vendu] FROM [sample4]
    ) AS t 
    PIVOT 
    (
        SUM([Nombre_Vendu]) FOR pivot_col IN ([2010],[2011])
    ) as p
 ORDER BY Produit,Couleur, _NAME_

3. Pivot dynamique avec la procédure stockée sp_transpose

Dans d'autres outils concurrents il existe des commandes plus "dynamiques" que l'opérateur PIVOT. Dans SAS par exemple, la commande TRANSPOSE est beaucoup plus riche et dynamique. Dans le cadre d'un projet de reprise de code SAS en TSQL, je me suis d'ailleurs basé sur cette commande SAS pour les fonctionnalités de ma procédure TSQL sp_transpose.

sp_transpose reçoit les paramètres suivants :

  @IN varchar(100) : nom de la table d'entrée
  @OUT varchar(100) : nom de la table de sortie. Attention, ne supporte pas les tables temporaires locales (les tables temporaires globales sont admises)
  @BY varchar(100) : colonne ou liste de colonnes non pivotées (regroupement)
  @ID varchar(100) : colonne de pivot (qui contient la liste des valeurs qui deviendront des colonnes dans la table pivotée)
  @VAR varchar(100) : colonnes ou liste de colonnes de valeurs à agréger et transposer
  @AGGR varchar(100) : fonction d'agrégation utilisée pour le pivot. Par défaut et si non précisé SUM est utilisé.
  @NONULL bit : passer à 1 pour remplacer les sommes NULL par des 0 dans le résultat du pivot

Ce qui donne, pour notre exemple 1 : 

exec sp_transpose @IN=sample1,@ID='Periode',@VAR='Nombre_Adhesions'

Et pour notre exemple 2 :

exec sp_transpose @IN=sample4,@BY='Produit,Couleur',@ID='Annee',@VAR='Nombre_Fabrique,Nombre_Vendu',@nonull=1

Le code de la procédure sp_transpose :

/*-------------------------------------------------------------------
[SCRIPT] Procédure sp_transpose
[DATABASE] Base utilisateur
[DESCRIPTION] Procédure pour transposer (pivot) une table façon SAS
[PARAMETRES]
  @IN varchar(100) : nom de la table d'entrée
  @OUT varchar(100) : nom de la table de sortie. Attention, ne supporte pas les tables temporaires locales (les tables temporaires globales sont admises)
  @BY varchar(100) : colonne non pivotée (regroupement)
  @ID varchar(100) : colonne de pivot (contient la liste des valeurs qui deviendront des colonnes dans la table pivotée)
  @VAR varchar(100) : colonnes de valeurs à agréger et transposer
  @AGGR varchar(100) : fonction d'agrégation utilisée pour le pivot. Par défaut et si non précisé SUM est utilisé.
  @NONULL bit : passer à 1 pour remplacer les sommes NULL par des 0 dans le résultat du pivot

[SYNTAXE] : exec sp_transpose @IN='#somme_adhesions', @OUT='##somme_adhesions_transpose', @BY='Nom', @ID='periode', @VAR='A_Nombre_Adhesion', @AGGR='SUM', @NONULL=0
[MAJ PAR] DATAFLY - Arian Papillon
[DATEMAJ] 11/11/2012 (version 2.1)
-------------------------------------------------------------------*/
ALTER PROCEDURE [dbo].[sp_transpose]
  @IN VARCHAR(100) = NULL
, @OUT VARCHAR(100) = NULL
, @BY VARCHAR(100) = NULL
, @ID VARCHAR(100) = NULL
, @VAR VARCHAR(100) = NULL
, @AGGR VARCHAR(100) = 'SUM'
, @NONULL BIT = 0
AS 
  DECLARE @pivot VARCHAR(MAX)
  , @sql VARCHAR(MAX)
  , @select VARCHAR(MAX)
  , @selectcol VARCHAR(MAX)
  , @sqlfull VARCHAR(MAX) = ''
  , @pos INT
  , @n INT = 0
  , @UVAR VARCHAR(30)

SET NOCOUNT ON
-- constitue la liste des colonnes
  SET @select = 'SELECT DISTINCT [' + @ID + '] as pivot_col FROM ' + @IN
  IF OBJECT_ID('tempdb.dbo.#pivot_columns') IS NOT NULL 
    DROP TABLE #pivot_columns
  CREATE TABLE #pivot_columns
    (
      pivot_column VARCHAR(100)
    )

  INSERT  INTO #pivot_columns
          EXEC ( @select
              )
  SELECT  @pivot = COALESCE(@pivot + ',', '') + '[' + pivot_column + ']'
  FROM    #pivot_columns
  
-- gestion des NULL
  IF @BY IS NULL 
    SET @selectcol = '_NAME_'
  ELSE 
    SET @selectcol = @BY + ',_NAME_'
  IF @NONULL = 1 
    SELECT  @selectcol = COALESCE(@selectcol + ',', '') + 'coalesce([' +
            pivot_column + '],0) AS [' + pivot_column + ']'
    FROM    #pivot_columns
  ELSE 
    SET @selectcol = '*'

  
-- Traite le cas de colonnes multiples dans @VAR
  SET @VAR = LTRIM(RTRIM(@VAR)) + ','
  SET @Pos = CHARINDEX(',', @VAR, 1)
  IF REPLACE(@VAR, ',', '') <> '' 
    BEGIN
      WHILE @Pos > 0
        BEGIN
          SET @UVAR = LTRIM(RTRIM(LEFT(@VAR, @Pos - 1)))

-- prépare le select sur la colonne pivot
          IF @BY IS NULL 
            SET @select = 'SELECT ''' + @UVAR + ''' AS _NAME_, [' + @ID +
              '] AS pivot_col, [' + @UVAR + '] FROM [' + @IN + ']'
          ELSE 
            SET @select = 'SELECT ' + @BY + ', ''' + @UVAR +
              ''' AS _NAME_, [' + @ID + '] AS pivot_col, [' + @UVAR +
              '] FROM [' + @IN + ']'
              
 -- sortie vers une table temporaire         
          IF @OUT IS NOT NULL AND
            @n = 0 
            SET @sql = '
    SELECT ' + @selectcol + ' INTO ' + @OUT + ' FROM'
          ELSE 
            SET @sql = '
    SELECT ' + @selectcol + ' FROM'
    
 -- constitution de la requête individuelle   
          SET @sql = @sql + '
    (
        ' + @select + '
    ) AS t 
    PIVOT 
    (
        ' + @AGGR + '([' + @UVAR + ']) FOR pivot_col IN (' + @pivot + ')
    ) as p
' 
          SET @VAR = RIGHT(@VAR, LEN(@VAR) - @Pos)
          SET @Pos = CHARINDEX(',', @VAR, 1)
          
-- plusieurs pivots : union         
          IF @pos > 0 
            SET @sqlfull = @sqlfull + @sql + ' 
UNION ALL 
'
          SET @n = @n + 1
        END
    END
    
-- order by
  IF @BY IS NULL 
    SET @sqlfull = @sqlfull + @sql + ' ORDER BY _NAME_'
  ELSE 
    SET @sqlfull = @sqlfull + @sql + ' ORDER BY ' + @BY + ', _NAME_'

-- exécution    
  EXEC ( @sqlfull
      )