Requête PIVOT dynamique du serveur SQL ?

Question

J'ai été chargé de trouver un moyen de traduire les données suivantes :

date        category        amount
1/1/2012    ABC             1000.00
2/1/2012    DEF             500.00
2/1/2012    GHI             800.00
2/10/2012   DEF             700.00
3/1/2012    ABC             1100.00

en ce qui concerne les éléments suivants :

date        ABC             DEF             GHI
1/1/2012    1000.00
2/1/2012                    500.00
2/1/2012                                    800.00
2/10/2012                   700.00
3/1/2012    1100.00

Les espaces vides peuvent être des NULL ou des espaces vides, l'un ou l'autre convient, et les catégories doivent être dynamiques. Une autre réserve possible est que nous exécuterons la requête dans une capacité limitée, ce qui signifie que les tables temporaires sont exclues. J'ai essayé de faire des recherches et j'ai abouti à PIVOT mais comme je ne l'ai jamais utilisé auparavant, je ne le comprends vraiment pas, malgré tous mes efforts pour le comprendre. Quelqu'un peut-il m'indiquer la bonne direction ?

Answer 1

PIVOT SQL dynamique :

create table temp
(
    date datetime,
    category varchar(3),
    amount money
)

insert into temp values ('1/1/2012', 'ABC', 1000.00)
insert into temp values ('2/1/2012', 'DEF', 500.00)
insert into temp values ('2/1/2012', 'GHI', 800.00)
insert into temp values ('2/10/2012', 'DEF', 700.00)
insert into temp values ('3/1/2012', 'ABC', 1100.00)

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.category) 
            FROM temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT date, ' + @cols + ' from 
            (
                select date
                    , amount
                    , category
                from temp
           ) x
            pivot 
            (
                 max(amount)
                for category in (' + @cols + ')
            ) p '

execute(@query)

drop table temp

Résultats :

Date                        ABC         DEF    GHI
2012-01-01 00:00:00.000     1000.00     NULL    NULL
2012-02-01 00:00:00.000     NULL        500.00  800.00
2012-02-10 00:00:00.000     NULL        700.00  NULL
2012-03-01 00:00:00.000     1100.00     NULL    NULL

Answer 2

PIVOT SQL dynamique

Approche différente pour la création de chaînes de colonnes

create table #temp
(
    date datetime,
    category varchar(3),
    amount money
)

insert into #temp values ('1/1/2012', 'ABC', 1000.00)
insert into #temp values ('2/1/2012', 'DEF', 500.00)
insert into #temp values ('2/1/2012', 'GHI', 800.00)
insert into #temp values ('2/10/2012', 'DEF', 700.00)
insert into #temp values ('3/1/2012', 'ABC', 1100.00)

DECLARE @cols  AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';

SELECT @cols = @cols + QUOTENAME(category) + ',' FROM (select distinct category from #temp ) as tmp
select @cols = substring(@cols, 0, len(@cols)) --trim "," at end

set @query = 
'SELECT * from 
(
    select date, amount, category from #temp
) src
pivot 
(
    max(amount) for category in (' + @cols + ')
) piv'

execute(@query)
drop table #temp

Résultat

date                    ABC     DEF     GHI
2012-01-01 00:00:00.000 1000.00 NULL    NULL
2012-02-01 00:00:00.000 NULL    500.00  800.00
2012-02-10 00:00:00.000 NULL    700.00  NULL
2012-03-01 00:00:00.000 1100.00 NULL    NULL

Answer 3

Je sais que cette question est plus ancienne mais j'ai regardé les réponses et j'ai pensé que je pourrais peut-être développer la partie "dynamique" du problème et éventuellement aider quelqu'un.

Avant tout, j'ai conçu cette solution pour résoudre un problème que rencontraient quelques collègues avec des ensembles de données incohérents et volumineux qui devaient être pivotés rapidement.

Cette solution nécessite la création d'une procédure stockée, donc si cela ne correspond pas à vos besoins, arrêtez de lire maintenant.

Cette procédure va prendre en compte les variables clés d'un tableau croisé dynamique afin de créer dynamiquement des tableaux croisés dynamiques pour différents tableaux, noms de colonnes et agrégats. La colonne Static est utilisée comme colonne de regroupement/identité pour le tableau croisé dynamique (elle peut être supprimée du code si elle n'est pas nécessaire, mais elle est assez courante dans les tableaux croisés dynamiques et était nécessaire pour résoudre le problème initial), la colonne pivot est celle à partir de laquelle les noms des colonnes résultantes seront générés, et la colonne valeur est celle à laquelle l'agrégat sera appliqué. Le paramètre Table est le nom de la table, y compris le schéma (schema.tablename). Cette partie du code mériterait d'être améliorée car elle n'est pas aussi propre que je le souhaiterais. Cela a fonctionné pour moi parce que mon utilisation n'était pas publique et que l'injection SQL n'était pas un problème. Le paramètre Aggregate acceptera n'importe quel agrégat sql standard 'AVG', 'SUM', 'MAX', etc. Le code propose aussi par défaut MAX comme agrégat, ce qui n'est pas nécessaire, mais le public pour lequel ce code a été conçu à l'origine ne comprenait pas les pivots et utilisait typiquement max comme agrégat.

Commençons par le code de création de la procédure stockée. Ce code devrait fonctionner dans toutes les versions de SSMS 2005 et supérieures, mais je ne l'ai pas testé en 2005 ou 2016, mais je ne vois pas pourquoi il ne fonctionnerait pas.

create PROCEDURE [dbo].[USP_DYNAMIC_PIVOT]
    (
        @STATIC_COLUMN VARCHAR(255),
        @PIVOT_COLUMN VARCHAR(255),
        @VALUE_COLUMN VARCHAR(255),
        @TABLE VARCHAR(255),
        @AGGREGATE VARCHAR(20) = null
    )

AS

BEGIN

SET NOCOUNT ON;
declare @AVAIABLE_TO_PIVOT NVARCHAR(MAX),
        @SQLSTRING NVARCHAR(MAX),
        @PIVOT_SQL_STRING NVARCHAR(MAX),
        @TEMPVARCOLUMNS NVARCHAR(MAX),
        @TABLESQL NVARCHAR(MAX)

if isnull(@AGGREGATE,'') = '' 
    begin
        SET @AGGREGATE = 'MAX'
    end

 SET @PIVOT_SQL_STRING =    'SELECT top 1 STUFF((SELECT distinct '', '' + CAST(''[''+CONVERT(VARCHAR,'+ @PIVOT_COLUMN+')+'']''  AS VARCHAR(50)) [text()]
                            FROM '+@TABLE+'
                            WHERE ISNULL('+@PIVOT_COLUMN+','''') <> ''''
                            FOR XML PATH(''''), TYPE)
                            .value(''.'',''NVARCHAR(MAX)''),1,2,'' '') as PIVOT_VALUES
                            from '+@TABLE+' ma
                            ORDER BY ' + @PIVOT_COLUMN + ''

declare @TAB AS TABLE(COL NVARCHAR(MAX) )

INSERT INTO @TAB EXEC SP_EXECUTESQL  @PIVOT_SQL_STRING, @AVAIABLE_TO_PIVOT 

SET @AVAIABLE_TO_PIVOT = (SELECT * FROM @TAB)

SET @TEMPVARCOLUMNS = (SELECT replace(@AVAIABLE_TO_PIVOT,',',' nvarchar(255) null,') + ' nvarchar(255) null')

SET @SQLSTRING = 'DECLARE @RETURN_TABLE TABLE ('+@STATIC_COLUMN+' NVARCHAR(255) NULL,'+@TEMPVARCOLUMNS+')  
                    INSERT INTO @RETURN_TABLE('+@STATIC_COLUMN+','+@AVAIABLE_TO_PIVOT+')

                    select * from (
                    SELECT ' + @STATIC_COLUMN + ' , ' + @PIVOT_COLUMN + ', ' + @VALUE_COLUMN + ' FROM '+@TABLE+' ) a

                    PIVOT
                    (
                    '+@AGGREGATE+'('+@VALUE_COLUMN+')
                    FOR '+@PIVOT_COLUMN+' IN ('+@AVAIABLE_TO_PIVOT+')
                    ) piv

                    SELECT * FROM @RETURN_TABLE'

EXEC SP_EXECUTESQL @SQLSTRING

END

Ensuite, nous allons préparer nos données pour l'exemple. J'ai repris l'exemple de données de la réponse acceptée en y ajoutant quelques éléments de données à utiliser dans cette démonstration de principe pour montrer les différents résultats de la modification globale.

create table temp
(
    date datetime,
    category varchar(3),
    amount money
)

insert into temp values ('1/1/2012', 'ABC', 1000.00)
insert into temp values ('1/1/2012', 'ABC', 2000.00) -- added
insert into temp values ('2/1/2012', 'DEF', 500.00)
insert into temp values ('2/1/2012', 'DEF', 1500.00) -- added
insert into temp values ('2/1/2012', 'GHI', 800.00)
insert into temp values ('2/10/2012', 'DEF', 700.00)
insert into temp values ('2/10/2012', 'DEF', 800.00) -- addded
insert into temp values ('3/1/2012', 'ABC', 1100.00)

Les exemples suivants montrent les différents énoncés d'exécution en montrant les différents agrégats comme un exemple simple. Je n'ai pas choisi de modifier les colonnes statique, pivot et valeur pour que l'exemple reste simple. Vous devriez pouvoir copier et coller le code pour commencer à le manipuler vous-même.

exec [dbo].[USP_DYNAMIC_PIVOT] 'date','category','amount','dbo.temp','sum'
exec [dbo].[USP_DYNAMIC_PIVOT] 'date','category','amount','dbo.temp','max'
exec [dbo].[USP_DYNAMIC_PIVOT] 'date','category','amount','dbo.temp','avg'
exec [dbo].[USP_DYNAMIC_PIVOT] 'date','category','amount','dbo.temp','min'

Cette exécution renvoie respectivement les ensembles de données suivants.

Answer 4

Version mise à jour pour SQL Server 2017 utilisant la fonction STRING_AGG pour construire la liste des colonnes pivotantes :

create table temp
(
    date datetime,
    category varchar(3),
    amount money
);

insert into temp values ('20120101', 'ABC', 1000.00);
insert into temp values ('20120201', 'DEF', 500.00);
insert into temp values ('20120201', 'GHI', 800.00);
insert into temp values ('20120210', 'DEF', 700.00);
insert into temp values ('20120301', 'ABC', 1100.00);

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

SET @cols = (SELECT STRING_AGG(category,',') FROM (SELECT DISTINCT category FROM temp WHERE category IS NOT NULL)t);

set @query = 'SELECT date, ' + @cols + ' from 
            (
                select date
                    , amount
                    , category
                from temp
           ) x
            pivot 
            (
                 max(amount)
                for category in (' + @cols + ')
            ) p ';

execute(@query);

drop table temp;

Answer 5

Vous pouvez y parvenir en utilisant le langage TSQL dynamique (n'oubliez pas d'utiliser QUOTENAME pour éviter les attaques par injection SQL) :

Pivots avec colonnes dynamiques dans SQL Server 2005

SQL Server - Table PIVOT dynamique - Injection SQL

Référence obligatoire à La malédiction et les bénédictions du SQL dynamique