Comment effectuer un pivotement dynamique avec plusieurs colonnes dans SQL Server 2008 ?

PIVOTAGE dynamique avec plusieurs colonnes dans SQL Server

Présentation

SQL Server offre des options limitées pour faire pivoter les données sur plusieurs colonnes . Cet article explique comment réaliser un pivotement dynamique dans SQL Server 2008 avec plusieurs colonnes en utilisant une combinaison de SQL dynamique et sans pivotement.

Comprendre le problème

L'objectif est de transformer une table avec plusieurs colonnes dans un format plus convivial où les valeurs des colonnes deviennent les en-têtes de colonne. Dans ce cas, le tableau contient les données suivantes :

ID	YEAR	TYPE	TOTAL	VOLUME
DD1	2008	A	1000	10
DD1	2008	B	2000	20
DD1	2008	C	3000	30
DD1	2009	A	4000	40
DD1	2009	B	5000	50
DD1	2009	C	6000	60
DD2	2008	A	7000	70
DD2	2008	B	8000	80
DD2	2008	C	9000	90
DD2	2009	A	10000	100
DD2	2009	B	11000	110
DD2	2009	C	1200	120

Le résultat souhaité est :

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	1200	120

Solution

Annuler le pivotement des données

Pour faire pivoter plusieurs colonnes, il faut d'abord annuler le pivotement des données. Cela signifie transformer les colonnes en lignes, avec une colonne col identifiant le nom de la colonne d'origine et une colonne valeur contenant la valeur.

select id, 
    col = cast(t_year as varchar(4))+'_'+t_type+'_'+col, 
    value
from ATM_TRANSACTIONS t
cross apply
(
    select 'total', total union all
    select 'volume', volume
) c (col, value);

Fonction PIVOT dynamique

L'étape suivante consiste à utiliser la Fonction PIVOT pour transformer les données non pivotées au format souhaité. Cependant, SQL Server présente des limites lors du pivotement de plusieurs colonnes. Pour surmonter ce problème, le SQL dynamique est utilisé pour construire la requête PIVOT au moment de l'exécution.

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

select @cols = STUFF((SELECT ',' + QUOTENAME(cast(t_year as varchar(4))+'_'+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, 
                    col = cast(t_year as varchar(4))+''_''+t_type+''_''+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 '

execute sp_executesql @query;

Conclusion

En combinant l'arrêt du pivotement avec le SQL dynamique, il est possible d'effectuer un pivotement dynamique sur plusieurs colonnes dans SQL. Server 2008. Cela fournit une solution flexible pour transformer les données dans un format plus convivial.

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!