Transformer les colonnes en lignes avec SSIS UNPIVOT

Transformer les colonnes d’une table SQL Server en lignes avec le composant de transformation SSIS Unpivot.

Pour transformer les colonnes en lignes avec le composant SSIS Unpivot, il n’est pas nécessaire de maitriser l’opérateur T-SQL UNPIVOT. En effet, il est plus pratique d’écrire une requête de sélection sur des données stockées de manière verticale plutôt qu’horizontale. Voici donc comment transformer des colonnes en lignes avec le composant SSIS Unpivot. Pour gérer les données d’une table SQL avec de la flexibilité, il est donc conseillé de stocker les données dans des lignes plutôt que dans les nom de colonnes.

Ce dernier cas rend la structure de la base de donnée moins facile à gérer. Même si au niveau espace disque et performance, sur des données mois par mois, stocker 12 fois moins de lignes peut être utile sur des projets SQL.

Tout d’abord, le but de ce tutoriel est de transformer des données mensuelles stockées en colonnes vers une table en ligne. La table source a donc 12 colonnes de données, soit une pour chaque mois de l’année. La table cible elle se compose de deux colonnes distinctes, soit la colonne pour stocker le nom du mois et une colonne pour stocker le montant des ventes.

1. Créer la table source avec les colonnes à pivoter en lignes

Commencer par créer le jeu de données pour avoir tous les éléments en main et comprendre cet exemple avant de l’adapter éventuellement à un cas plus concret. L’objectif est de fournir tout le matériel sur ce tutoriel pour se concentrer sur le composant Unpivot et son fonctionnement.

Depuis une connexion SSMS, exécuter le code SQL suivant, qui est de type LDD (Langage de Définition de Données). Il permet ainsi de créer la table dbo.Sales_Monthly_In_Columns avec une colonne de données par mois et une ligne par année.

CREATE TABLE [dbo].[Sales_Monthly_In_Columns]
(
  [CustomerID] int NOT NULL,
  [YearID]     nvarchar(20) NOT NULL,
  [January]    numeric(10,2)  NULL,
  [February]   numeric(10,2)  NULL,
  [March]      numeric(10,2)  NULL,
  [April]      numeric(10,2)  NULL,
  [May]        numeric(10,2)  NULL,
  [June]       numeric(10,2)  NULL,
  [July]       numeric(10,2)  NULL,
  [August]     numeric(10,2)  NULL,
  [September]  numeric(10,2)  NULL,
  [October]    numeric(10,2)  NULL,
  [November]   numeric(10,2)  NULL,
  [December]   numeric(10,2)  NULL
  CONSTRAINT [SalesMonthlyColumnsPK] 
    PRIMARY KEY CLUSTERED (
      [CustomerID], [YearID] ASC
    )
);
GO

2. Insérer les données de ventes mensuelles en colonnes

Insérer ensuite le jeu de données avec les ventes pour les années 2021 et 2022. Les données concernent 8 clients différents, identifiés de manière unique par le numéro de client, CustomerID.

INSERT [dbo].[Sales_Monthly_In_Columns] VALUES (1, N'2021', 221.80, 2658.80, 2485.00, 6302.80, 7605.60, 5288.50, 3335.20, 5216.40, 4372.20, 9815.60, 5479.00, 9212.10 );
INSERT [dbo].[Sales_Monthly_In_Columns] VALUES (1, N'2022', 7575.90, 6662.60, 1464.60, 7011.10, 3978.30, 4101.10, 5964.80, 2619.60, 4482.00, 3874.00, 3171.80, 1610.70 );
INSERT [dbo].[Sales_Monthly_In_Columns] VALUES (2, N'2021', 6263.30, 1952.50, 9110.00, 5433.30, 1177.90, 9418.80, 8657.90, 5264.80, 595.80, 6332.50, 3268.50, 3413.50 );
INSERT [dbo].[Sales_Monthly_In_Columns] VALUES (2, N'2022', 2969.90, 3022.70, 1626.10, 9396.30, 8723.30, 9784.80, 9084.10, 3366.40, 8784.50, 1313.50, 7135.00, 8379.10 );
INSERT [dbo].[Sales_Monthly_In_Columns] VALUES (3, N'2021', 2618.90, 5898.70, 7929.10, 5614.40, 7561.80, 5545.80, 2666.50, 2229.70, 1500.50, 6051.40, 1992.20, 1060.80 );
INSERT [dbo].[Sales_Monthly_In_Columns] VALUES (3, N'2022', 2433.10, 8977.80, 9414.60, 4195.10, 6623.20, 1783.40, 1480.30, 4175.00, 2497.20, 6332.10, 6839.20, 145.50 );
INSERT [dbo].[Sales_Monthly_In_Columns] VALUES (4, N'2021', 7170.40, 3507.00, 9154.70, 4465.00, 1109.30, 1905.70, 7980.70, 9227.50, 2166.00, 2988.30, 6353.80, 9448.70 );
INSERT [dbo].[Sales_Monthly_In_Columns] VALUES (4, N'2022', 3287.60, 1805.70, 2068.20, 8353.40, 1378.70, 4689.10, 4720.00, 5643.60, 2722.50, 5539.00, 5494.60, 1790.60 );
INSERT [dbo].[Sales_Monthly_In_Columns] VALUES (5, N'2021', 3336.20, 7451.40, 1656.60, 978.30, 8794.60, 7568.30, 3547.80, 8382.20, 3068.10, 7882.30, 5726.60, 9693.10 );
INSERT [dbo].[Sales_Monthly_In_Columns] VALUES (5, N'2022', 1365.70, 3680.60, 8256.40, 5379.20, 1075.50, 2852.70, 4520.80, 7253.10, 3285.60, 705.10, 2134.60, 7437.00 );
INSERT [dbo].[Sales_Monthly_In_Columns] VALUES (6, N'2021', 1421.30, 2552.40, 174.20, 9158.00, 8138.50, 8458.30, 6548.90, 4805.80, 3026.50, 8127.40, 6626.40, 1035.70 );
INSERT [dbo].[Sales_Monthly_In_Columns] VALUES (6, N'2022', 2253.40, 1107.30, 9349.30, 3751.80, 9320.90, 2485.60, 435.30, 6358.10, 733.10, 1015.90, 7780.70, 6232.40 );
INSERT [dbo].[Sales_Monthly_In_Columns] VALUES (7, N'2021', 3971.30, 1538.20, 7727.50, 4494.10, 6314.70, 640.10, 6070.30, 5926.00, 7013.10, 2119.20, 3188.20, 4474.20 );
INSERT [dbo].[Sales_Monthly_In_Columns] VALUES (7, N'2022', 7082.40, 3010.10, 8716.00, 8493.50, 1782.80, 3023.20, 3897.60, 1752.80, 5952.30, 8430.40, 6643.10, 6276.10 );
INSERT [dbo].[Sales_Monthly_In_Columns] VALUES (8, N'2021', 7279.40, 4494.40, 7045.30, 4253.30, 6702.40, 9017.20, 810.00, 7891.80, 663.10, 5771.40, 3190.10, 6130.00 );
INSERT [dbo].[Sales_Monthly_In_Columns] VALUES (8, N'2022', 8253.70, 7844.60, 3655.80, 1046.10, 290.10, 5580.40, 3572.70, 9926.10, 6213.90, 7622.80, 3414.60, 4581.40 );

3. Créer la table cible pour stocker les ventes en lignes

Créer maintenant la deuxième table SQL, avec cette fois les mois en lignes. Les colonnes de regroupement, c’est à dire le numéro de client et l’année sont toujours présentes.

CREATE TABLE [dbo].[Sales_Monthly_In_Lines]
(
  [CustomerID]  int NOT NULL,
  [YearID]      nvarchar(20) NOT NULL,
  [MonthName]   nvarchar(10) NOT NULL,
  [SalesAmount] numeric(10,2)  NULL
  CONSTRAINT [SalesMonthlyLinesPK] 
    PRIMARY KEY CLUSTERED (
      [CustomerID], [YearID], [MonthName] ASC
    )
);
GO

4. Ajouter les composants SSIS OLE DB et SSIS UNPIVOT et configurer la connexion

Voici maintenant les étapes clefs de ce cours pour transformer les colonnes en lignes avec SSIS Unpivot. C’est à dire la configuration du flux de données. Ouvrir un package SSIS existant ou créer un nouveau projet et un package avec Visual Studio. Au préalable, créer une connexion à la base de données SQL Server, nécessaire pour exécuter le package. Ajouter ensuite un flux de données et y insérer les trois composants utiles.

• OLE DB Source : à faire pointer sur la table Sales_Monthly_In_Columns
• Composant SSIS Unpivot : à paramétrer pour transposer les mois et leur valeur en lignes
• OLE DB Destination : pointer sur la table cible Sales_Monthly_In_Lines avec la structure en lignes.

5. Configurer la table source et les colonnes à pivoter avec SSIS

Sélectionner la table avec la structure en colonnes comme source pour le package SSIS.

Vérifier aussi les colonnes présentes depuis l’onglet Columns, les 12 mois sont présents.

6. Configurer le composant SSIS UNPIVOT pour transformer les colonnes en lignes

Suivre ces étapes pour configurer le composant SSIS Unpivot pour transposer les colonnes en lignes.

1. Sélectionner les colonnes d’agrégations avec l’option Pass Through : ici c’est CustomerID et YearID.
2. Choisir toutes les colonnes à transposer : les colonnes des 12 mois de janvier à décembre.
3. Entrer le nom de la nouvelle colonne où stocker les ventes : SalesAmount.
4. Taper le nom de la colonne pour la clef de pivot, ou stocker le nom des mois : MonthName.

7. Gérer le warning de troncation affiché par SSIS

Comme dans beaucoup de composants sources ou de transformation de données, SQL Server Integration Services assigne aux nouvelles colonnes un type de données par défaut qui est (DT_WSTR,255). Ce qui ainsi provoque ce warning dans le composant cible.

[OLE DB Destination] Warning: Truncation may occur due to inserting data from data flow column « MonthName » with a length of 255 to database column « MonthName » with a length of 10.

En effet, la nouvelle colonne MonthName en sortie du bloc Unpivot est de type DT_WSTR avec une longueur de 255, tandis que la colonne dans la table est de type NVARCHAR(10).

La solution pour gérer ce comportement et supprimer l’avertissement est donc de changer le type de la colonne en sortie dans l’éditeur avancé du bloc Unpivot SSIS.

1. Faire d’abord un clic-droit sur le composant Unpivot SSIS et choisir Show Advanced Editor… puis choisir Input and Output Properties.
2. Depuis la section Unpivot Output, sélectionner la colonne MonthName à éditer.
3. Changer enfin la longueur de 255 caractères à 10 caractères.

Après cette adaptation de la longueur à la table cible, le package peut-être exécuter de manière fluide et sans warnings dans les logs SSIS à chaque exécution.

8. Paramétrer la table cible avec la structure en lignes

Ouvrir maintenant la destination et choisir la deuxième table avec une structure de données en lignes.

Vérifier le mapping, et mapper au besoin les colonnes en se basant sur le nom des colonnes.

1. Ouvrir l’éditeur OLE DB et faire un clic-droit
2. Sélectionner alors Map Items by Matching Names

9. Exécuter le package SSIS pour transposer les colonnes en lignes

Exécuter enfin le package pour pivoter les 16 lignes sources en 16*12 lignes, soit 192 lignes au total.

10. Comparer les colonnes sources avec les lignes cibles depuis SSMS

Pour vérifier le bon fonctionnement du package SSIS, se connecter d’abord à SSMS et exécuter ces deux requêtes SQL.

1. Une première requête SQL pour lire la table source en colonnes.
2. Une deuxième requête SQL pour lire la table transformée en lignes.

select * from [dbo].[Sales_Monthly_In_Columns];

select	*
from	[dbo].[Sales_Monthly_In_Lines]
order by YearID, CustomerID, MONTH(MonthName + ' 01, 1900');

Enfin, les montants de la première table sont présents dans la deuxième avec une structure en lignes.

Conclusion sur la transformation SSIS Unpivot

Transformer les colonnes en lignes avec SSIS et la transformation UNPIVOT se fait donc de manière simple et guidée, voire presque automatisée. Il suffit donc de gérer éventuellement les longueurs des colonnes des clefs de PIVOT. Un prérequis tout de même est de comprendre comment fonctionne l’opérateur UNPIVOT classique en T-SQL.

Autres techniques pour pivoter des données avec Microsoft

• Pivoter des données avec une requête SQL Server
• Pivoter des données avec Excel

Tutoriels SSIS pour débutants