Comment faire pivoter dynamiquement plusieurs colonnes dans SQL Server ?

Transformation de pivot dynamique sur plusieurs colonnes dans SQL Server

Présentation

Cet article aborde le défi d'effectuer une opération de pivot dynamique sur plusieurs colonnes dans Microsoft Serveur SQL. L'objectif est de transformer les données dans un format de tableau croisé dynamique, avec des lignes représentant des valeurs distinctes dans une colonne spécifiée et des colonnes représentant des valeurs d'autres colonnes.

Annuler le pivotement des données

Avant d'effectuer l'opération de pivotement, les données doivent être non pivotées. Ce processus remodèle les données en convertissant plusieurs colonnes en lignes. Dans ce cas, les colonnes Total et Volume ne sont pas pivotées.

SELECT ID, 
    CONCAT(T_YEAR, '_', T_TYPE, '_', COL) AS COL, 
    VALUE
FROM ATM_TRANSACTIONS t
CROSS APPLY
(
    SELECT 'total', TOTAL UNION ALL
    SELECT 'volume', VOLUME
) c (COL, VALUE)

Génération de la requête pivot dynamique

Une fois les données non pivotées, la requête pivot dynamique peut être construite. Cette requête crée dynamiquement les colonnes nécessaires pour le tableau croisé dynamique en fonction des données non pivotées.

DECLARE @cols AS NVARCHAR(MAX),
    @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(CONCAT(T_YEAR, '_', T_TYPE, '_', COL)) 
                    FROM ATM_TRANSACTIONS t
                    CROSS APPLY
                    (
                        SELECT 'total', 1 UNION ALL
                        SELECT 'volume', 2
                    ) c (COL, SO)
                    GROUP BY COL, SO, T_TYPE, T_YEAR
                    ORDER BY T_YEAR, T_TYPE, SO
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

SET @query = 'SELECT ID,' + @cols + ' 
            FROM 
            (
                SELECT ID, 
                    CONCAT(T_YEAR, ''_''+T_TYPE+''_''+COL) AS COL, 
                    VALUE
                FROM ATM_TRANSACTIONS t
                CROSS APPLY
                (
                    SELECT ''total'', TOTAL UNION ALL
                    SELECT ''volume'', VOLUME
                ) c (COL, VALUE)
            ) x
            PIVOT 
            (
                MAX(VALUE)
                FOR COL IN (' + @cols + ')
            ) p ';

Exécution de la requête dynamique

La requête dynamique est exécutée à l'aide de la procédure stockée sp_executesql. Cette procédure prend la requête générée par le code SQL dynamique et l'exécute.

EXECUTE SP_EXECUTESQL @QUERY;

Résultat

La sortie de l'opération pivot dynamique est un tableau croisé dynamique avec des valeurs d'ID distinctes sous forme de lignes et de colonnes. représentant les valeurs Total et Volume non pivotées pour chaque combinaison de T_YEAR et T_TYPE. Le résultat sera le suivant :

ID	2008_A_TOTAL	2008_A_VOLUME	2008_B_TOTAL	2008_B_VOLUME	2008_C_TOTAL	2008_C_VOLUME	2009_A_TOTAL	2009_A_VOLUME	2009_B_TOTAL	2009_B_VOLUME	2009_C_TOTAL	2009_C_VOLUME
DD1	1000	10	2000	20	3000	30	4000	40	5000	50	6000	60
DD2	7000	70	8000	80	9000	90	10000	100	11000	110	12000	120

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!