Transformer les colonnes en lignes avec SSIS Unpivot

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

Il est plus pratique d’effectuer une requête de sélection sur des données stockées de manière verticale plutôt qu’horizontale, voici comment transformer des colonnes en lignes avec SSIS et Unpivot. Pour gérer les données d’une table SQL avec de la flexibilité, il est recommandé 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 flexible. Même si au niveau stockage et performance, sur des données mensuelles, stocker 12 fois moins de lignes peut être utile sur certains projets.

Pivoter des colonnes SQL en lignes avec un package Integration Services et Unpivot

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

1. Créer les tables SQL et insérer les données en colonnes

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.

1.1 Créer la table source avec les données en colonnes

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

1.2 Insérer les données avec les 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 );

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

2. Ajouter les composants SSIS au flux de données

Voici maintenant la principale étape du tutoriel, 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.

2.1 Ajouter les composants et configurer la source OLE DB

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

2.2 Utiliser en source la table avec les colonnes à pivoter

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.

2.3 Configurer SSIS Unpivot pour transformer les colonnes

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.

2.4 Gérer le warning Integration Services de troncation après Unpivot

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.

2.5 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

3. Exécuter le package SSIS et transformer les colonnes en lignes

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

4. Comparer les colonnes sources avec les lignes cibles avec 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 pour lire la table source en colonnes.
2. Une deuxième pour 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 Unpivot SSIS

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

Tutoriels sur le pivot de données avec Microsoft

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

Tutoriels SSIS