SQL Server dynamique de PIVOT de la requête?

SQL dynamique PIVOT:

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

Je sais que cette question est plus vieux mais j'ai été en regardant à travers les réponses et pensé que je pourrais être en mesure de se développer sur la "dynamique" partie du problème et peut-être aider quelqu'un à sortir.

D'abord et avant tout, j'ai construit cette solution pour résoudre un problème d'un couple de collègues de travail avaient avec inconstant et de grands ensembles de données qui ont besoin d'être pivoté rapidement.

Cette solution nécessite la création d'une procédure stockée donc, si c'est hors de question pour vos besoins s'il vous plaît arrêter de lire maintenant.

Cette procédure va prendre dans les principales variables d'un pivot déclaration de créer dynamiquement pivot consolidés pour les différentes tables, les noms de colonnes et les agrégats. La Statique de la colonne est utilisée en tant que groupe par /colonne d'identité pour le pivot(ce peut être dépouillé de ce code si pas nécessaire, mais est assez commun en pivot états et était nécessaire pour résoudre le problème d'origine), le pivot de la colonne est où la fin de la résultante des noms de colonne sera généré à partir de, et la valeur de la colonne est ce que l'ensemble sera appliqué. Le paramètre de Table est le nom de la table dont le schéma (schéma.tablename) cette portion de code pourrait utiliser un peu d'amour, car il n'est pas aussi propre que je voudrais qu'il soit. Il a travaillé pour moi, parce que ma consommation n'a pas été orienté public et de l'injection sql n'a pas été un sujet de préoccupation. Le total paramètre acceptera aucune norme d'agrégation sql 'MOY', 'SOMME', 'MAX' etc. Le code par défaut de MAX comme un agrégat, ce n'est pas nécessaire, mais le public présent a été construit à l'origine pour ne pas comprendre pivots et ont été généralement à l'aide de max comme un agrégat.

Permet de commencer avec le code pour créer la procédure stockée. Ce code doit fonctionner dans toutes les versions de SSMS 2005 et au-dessus, mais je ne l'ai pas testé en 2005 ou en 2016, mais je ne vois pas pourquoi cela 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 récupérer nos données sont prêtes pour l'exemple. J'ai pris l'exemple de données à partir de la accepté de répondre avec l'ajout d'un couple d'éléments de données à utiliser dans cette preuve de concept pour montrer la variété des sorties de l'ensemble du changement.

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 la diversité de l'exécution des déclarations montrant la diversité des agrégats, comme un exemple simple. Je n'ai pas le choix de changer la statique, de pivot, et la valeur des colonnes à rester sur un exemple simple. Vous devriez être en mesure de simplement copier et coller le code pour commencer à jouer avec elle-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 les ensembles de données suivants, respectivement.

Il y a ma solution le nettoyage de la unnecesary les valeurs null

DECLARE @cols AS NVARCHAR(MAX),
@maxcols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(CodigoFormaPago) 
from PO_FormasPago
order by CodigoFormaPago
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')
select @maxcols = STUFF((SELECT ',MAX(' + QUOTENAME(CodigoFormaPago) + ') as ' + QUOTENAME(CodigoFormaPago)
from PO_FormasPago
order by CodigoFormaPago
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT CodigoProducto, DenominacionProducto, ' + @maxcols + '
FROM
(
SELECT 
CodigoProducto, DenominacionProducto,
' + @cols + ' from 
(
SELECT 
p.CodigoProducto as CodigoProducto,
p.DenominacionProducto as DenominacionProducto,
fpp.CantidadCuotas as CantidadCuotas,
fpp.IdFormaPago as IdFormaPago,
fp.CodigoFormaPago as CodigoFormaPago
FROM
PR_Producto p
LEFT JOIN PR_FormasPagoProducto fpp
ON fpp.IdProducto = p.IdProducto
LEFT JOIN PO_FormasPago fp
ON fpp.IdFormaPago = fp.IdFormaPago
) xp
pivot 
(
MAX(CantidadCuotas)
for CodigoFormaPago in (' + @cols + ')
) p 
)  xx 
GROUP BY CodigoProducto, DenominacionProducto'
t @query;
execute(@query);

Version mise à jour pour SQL Server 2017 à l'aide de STRING_AGG fonction pour construire le pivot de la colonne de la liste:

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;