Pivoter les lignes en colonnes avec SSIS

Transformer les lignes d’une table SQL Server en colonnes dans un package Integration Services avec la transformation Pivot.

Integration Services permet nativement de pivoter les lignes d’une table SQL Server ou d’un fichier en colonnes avec le composant SSIS Pivot. Mais comment réaliser l’opération en pratique avec un package SSIS? Voici donc un tutoriel pas à pas pour paramétrer le composant SSIS de Pivot et transformer les lignes d’une table SQL en colonnes, soit dans notre exemple une colonne cible par mois de l’année. Cet exemple simple utilise une seule colonne non pivotée car le composant ne permet pas de pivoter plus d’une colonne sans utiliser l’éditeur avancé et les numéros de colonnes internes à SSIS, appelées LineageID.

Le pivot de données est couramment utilisé dans les tableaux croisés dynamiques par exemple. Ce tutoriel Integration Services en 4 grandes étapes explique donc comment pivoter une table SQL basique depuis une structure verticale en lignes vers une structure horizontale en colonnes.

1. Créer la table source avec les mois en lignes

Tout d’abord, créer les 2 tables utilisées, soit une table source en lignes et la table de destination en colonnes. En effet, créer la table des ventes avec les valeurs mensuelles en lignes.

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

2. Insérer le jeu de données mensuelles

Insérer un jeu de données pour remplir la table sur 2 années.

INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'01_January', 221.80 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'02_February', 2658.80 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'03_March', 2485.00 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'04_April', 6302.80 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'05_May', 7605.60 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'06_June', 5288.50 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'07_July', 3335.20 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'08_August', 5216.40 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'09_September', 4372.20 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'10_October', 9815.60 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'11_November', 5479.00 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'12_December', 9212.10 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'01_January', 7575.90 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'02_February', 6662.60 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'03_March', 1464.60 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'04_April', 7011.10 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'05_May', 3978.30 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'06_June', 4101.10 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'07_July', 5964.80 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'08_August', 2619.60 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'09_September', 4482.00 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'10_October', 3874.00 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'11_November', 3171.80 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'12_December', 1610.70 );

3. Créer la table cible avec une colonne par mois

Créer enfin la table pour recevoir les données après le pivot qui contient chaque mois dans une colonne dédiée.

CREATE TABLE [dbo].[Sales_Monthly_In_Columns]
(
  [YearID]     nvarchar(20) NOT NULL,
  [01_January]    numeric(10,2)  NULL,
  [02_February]   numeric(10,2)  NULL,
  [03_March]      numeric(10,2)  NULL,
  [04_April]      numeric(10,2)  NULL,
  [05_May]        numeric(10,2)  NULL,
  [06_June]       numeric(10,2)  NULL,
  [07_July]       numeric(10,2)  NULL,
  [08_August]     numeric(10,2)  NULL,
  [09_September]  numeric(10,2)  NULL,
  [10_October]    numeric(10,2)  NULL,
  [11_November]   numeric(10,2)  NULL,
  [12_December]   numeric(10,2)  NULL
  CONSTRAINT [SalesMonthlyColumnsPK] 
    PRIMARY KEY CLUSTERED (
      [YearID] ASC
    )
);
GO

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

Cette partie du tutoriel est la partie au cœur du sujet, car il s’agit de paramétrer le flux de données. Au préalable, il faut bien sûr créer un package dans un projet SSIS et configurer une connexion à une base SQL Server. Ajouter ces 3 composants dans un flux de données existant, ou en créer un nouveau.

1. Une connexion de type OLE DB Source
2. Une transformation Pivot
3. Une connexion de type OLE DB Destination

5. Configurer la source du flux de données SSIS de Pivot

Sélectionner la table Sales_Monthly_In_Lines comme source du composant OLE DB Source.

6. Configurer le Pivot SSIS pour pivoter les lignes en colonnes

Configurer maintenant le Pivot SSIS comme suit:

1. Choisir la clef de pivot dont les valeurs deviennent le nom des Nouvelles colonnes : MonthName
2. Choisir la colonne clef qui restera présente en sortie : YearID
3. Sélectionner la colonne source qui contient les valeurs : SalesAmount
4. Entrer la liste des colonnes créées par le Pivot, séparées par une virgule : 01_Janvier à 12_Décembre
5. Générer les colonnes pour remplir la zone de droite avec les nouvelles colonnes pivotées.

La liste des noms de colonnes est la suivante, sans espaces car le composant SSIS Pivot utilise les espaces dans les noms de colonnes générés automatiquement.

01_January,02_February,03_March,04_April,05_May,06_June,07_July,08_August,09_September,10_October,11_November,12_December 

Il n’est possible de renseigner une seule colonne Clef pour pivoter les colonnes. Pour pivoter une table avec plus de colonnes de regroupement, utiliser l’éditeur avancé et les identifiants de colonnes SSIS, ceci fera l’objet d’un autre tutoriel dédié au Pivot avec plusieurs colonnes fixes.

7. Configurer la table de destination avec les mois en colonnes

Sélectionner la table de destination pour stocker dans la base SQL Server les données après le Pivot. C’est la table dbo.Sales_Monthly_In_Columns crée à l’étape 1.3 du tutoriel.

Depuis l’onglet Mappings, relier chaque colonne source avec la colonne cible correspondante, soit les 12 colonnes générées pour chaque mois de données.

Une fois tous les composants configurés, Visual Studio n’affiche aucune erreur ni avertissement, dans le cas contraire, vérifier les liaisons et les type de données.

8. Exécuter le package SSIS

Exécuter maintenant le package pour transférer et pivoter les données SQL Server du format en lignes vers un format en colonnes. Les 24 lignes du jeu de données, soit 24 mois, sont pivotées en seulement 2 lignes, soit 2 années.

9. Vérifier les données après le Pivot depuis SSMS

Une fois le package exécuter, se connecter à la base de données avec SSMS (SQL Server Management Studio) et exécuter ces 2 requêtes de sélection de données. Le résultat est cohérent avec pour la première table les données pour les années 2021 et 2022 en ligne. Et pour la deuxième, les mêmes montants avec 2021 sur une seule ligne et l’année 2020 sur une autre ligne.

select * from [dbo].[Sales_Monthly_In_Lines];

select * from [dbo].[Sales_Monthly_In_Columns];

Conclusion sur l’utilisation du Pivot SSIS avec une table simple

Pour conclure sur cet article qui explique comment pivoter les lignes en colonnes avec SSIS Pivot, le composant natif permet facilement de pivoter les lignes d’une table SQL, mais cette dernière doit avoir une structure simple. C’est à dire qu’il gère une seule colonne de regroupement, statique. Pour des cas plus complexes, il est recommandé de passer par la requête T-SQL PIVOT plus flexible et plus facile à maintenir.

Tutoriels sur le Pivot ou Unpivot avec les technologies Microsoft

• Transformer les colonnes en lignes avec SSIS Unpivot
• Pivoter une table avec une requête SQL Server
• Pivoter les lignes avec Excel
• Transposer les colonnes en lignes avec SQL UNPIVOT