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

Voici ma solution pour nettoyer les valeurs nulles inutiles

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);

Answer 2

Le code ci-dessous fournit les résultats qui remplacent NULL a zéro dans le résultat.

Création de tableaux et insertion de données :

create table test_table
 (
 date nvarchar(10),
 category char(3),
 amount money
 )

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

Requête pour générer les résultats exacts qui remplace également NULL par des zéros :

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX),
@PivotColumnNames AS NVARCHAR(MAX),
@PivotSelectColumnNames AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column
SELECT @PivotColumnNames= ISNULL(@PivotColumnNames + ',','')
+ QUOTENAME(category)
FROM (SELECT DISTINCT category FROM test_table) AS cat

--Get distinct values of the PIVOT Column with isnull
SELECT @PivotSelectColumnNames 
= ISNULL(@PivotSelectColumnNames + ',','')
+ 'ISNULL(' + QUOTENAME(category) + ', 0) AS '
+ QUOTENAME(category)
FROM (SELECT DISTINCT category FROM test_table) AS cat

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
N'SELECT date, ' + @PivotSelectColumnNames + '
FROM test_table
pivot(sum(amount) for category in (' + @PivotColumnNames + ')) as pvt';

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

SORTIE :

Answer 3

CREATE TABLE #PivotExample(
   [ID]      [nvarchar](50)   NULL,       
   [Description]   [nvarchar](50)   NULL,
   [ClientId]   [smallint] NOT NULL,
)
GO

INSERT   #PivotExample  ([ID],[Description],   [ClientId])   VALUES ('ACI1','ACI1Desc1',1008)
INSERT   #PivotExample  ([ID],[Description],   [ClientId])   VALUES ('ACI1','ACI1Desc2',2000)
INSERT   #PivotExample  ([ID],[Description],   [ClientId])   VALUES ('ACI1','ACI1Desc3',3000)
INSERT   #PivotExample  ([ID],[Description],   [ClientId])   VALUES ('ACI1','ACI1Desc4',4000)
INSERT   #PivotExample  ([ID],[Description],   [ClientId])   VALUES ('ACI2','ACI2Desc1',5000)
INSERT   #PivotExample  ([ID],[Description],   [ClientId])   VALUES ('ACI2','ACI2Desc2',6000)
INSERT   #PivotExample  ([ID],[Description],   [ClientId])   VALUES ('ACI2','ACI2Desc3', 7000)

SELECT * FROM #PivotExample

--Declare necessary variables
DECLARE   @SQLQuery AS NVARCHAR(MAX)
DECLARE   @PivotColumns AS NVARCHAR(MAX)

--Get unique values of pivot column  
SELECT   @PivotColumns= COALESCE(@PivotColumns + ',','') + QUOTENAME([Description])
FROM (SELECT DISTINCT [Description] FROM [dbo].#PivotExample) AS PivotExample

--SELECT   @PivotColumns

--Create the dynamic query with all the values for 
--pivot column at runtime
SET   @SQLQuery = 
    N' -- Your pivoted result comes here
    SELECT ID, ' + @PivotColumns + '
    FROM 
    (
        -- Source table should in a inner query
        SELECT ID,[Description],[ClientId]
        FROM #PivotExample
    )AS P
    PIVOT
    (     
          -- Select the values from derived table P
          SUM(ClientId) 
          FOR [Description] IN (' + @PivotColumns + ') 
    )AS PVTTable'

--SELECT   @SQLQuery
--Execute dynamic query
EXEC sp_executesql @SQLQuery

Drop table #PivotExample

Answer 4

Une méthode entièrement générique qui fonctionnera dans des environnements MS SQL non traditionnels (par exemple, un système d'information sur la santé).

-- DROP PROCEDURE IF EXISTS
if object_id('dbo.usp_generic_pivot') is not null 
     DROP PROCEDURE dbo.usp_generic_pivot
GO;

CREATE PROCEDURE dbo.usp_generic_pivot (
    @source NVARCHAR (100), -- table or view object name
   @pivotCol NVARCHAR (100), -- the column to pivot
   @pivotAggCol NVARCHAR (100), -- the column with the values for the pivot
   @pivotAggFunc NVARCHAR (20), -- the aggregate function to apply to those values
   @leadCols NVARCHAR (100) -- comma seprated list of other columns to keep and order by
)
AS
BEGIN
   DECLARE @pivotedColumns NVARCHAR(MAX)
   DECLARE @tsql NVARCHAR(MAX)

   SET @tsql = CONCAT('SELECT @pivotedColumns = STRING_AGG(qname, '','') FROM (SELECT DISTINCT QUOTENAME(', @pivotCol,') AS qname FROM ',@source, ') AS qnames')
   EXEC sp_executesql @tsql, N'@pivotedColumns nvarchar(max) out', @pivotedColumns out

   SET @tsql = CONCAT ( 'SELECT ', @leadCols,   ',', @pivotedColumns,' FROM ',' ( SELECT ',@leadCols,',',
         @pivotAggCol,',',   @pivotCol,   ' FROM ',   @source,   ') as t ',
         ' PIVOT (', @pivotAggFunc,   '(', @pivotAggCol,   ')',' FOR ',   @pivotCol,
         '   IN (', @pivotedColumns,')) as pvt ',' ORDER BY ',   @leadCols)

   EXEC (@tsql)

END
GO;

-- TEST EXAMPLE
EXEC dbo.usp_generic_pivot  
    @source  = '[your_db].[dbo].[form_answers]',
   @pivotCol  = 'question', 
   @pivotAggCol   = 'answer',
   @pivotAggFunc  = 'MAX', 
   @leadCols    = 'candidate_id, candidate_name' 
GO;