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

完全通用的方式，将在非传统的MS SQL环境中工作(例如Azure Synapse Analytics无服务器SQL池)-它在SPROC中，但不需要这样使用…

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

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

Taryn的答案的一个版本的性能改进:

Data

CREATE TABLE dbo.Temp
(
    [date] datetime NOT NULL,
    category nchar(3) NOT NULL,
    amount money NOT NULL,

    INDEX [CX dbo.Temp date] CLUSTERED ([date]),
    INDEX [IX dbo.Temp category] NONCLUSTERED (category)
);

INSERT dbo.Temp
    ([date], category, amount)
VALUES
    ({D '2012-01-01'}, N'ABC', $1000.00),
    ({D '2012-01-02'}, N'DEF', $500.00),
    ({D '2012-01-02'}, N'GHI', $800.00),
    ({D '2012-02-10'}, N'DEF', $700.00),
    ({D '2012-03-01'}, N'ABC', $1100.00);

动态主

DECLARE 
    @Delimiter nvarchar(4000) = N',',
    @DelimiterLength bigint,
    @Columns nvarchar(max),
    @Query nvarchar(max);

SET @DelimiterLength = LEN(REPLACE(@Delimiter, SPACE(1), N'#'));

-- Before SQL Server 2017
SET @Columns =
    STUFF
    (
        (
            SELECT 
                [text()] = @Delimiter,
                [text()] = QUOTENAME(T.category)
            FROM dbo.Temp AS T
            WHERE T.category IS NOT NULL
            GROUP BY T.category
            ORDER BY T.category
            FOR XML PATH (''), TYPE
        )
        .value(N'text()[1]', N'nvarchar(max)'),
        1, @DelimiterLength, SPACE(0)
    );

-- Alternative for SQL Server 2017+ and database compatibility level 110+
SELECT @Columns = 
    STRING_AGG(CONVERT(nvarchar(max), QUOTENAME(T.category)), N',')
        WITHIN GROUP (ORDER BY T.category)
FROM 
(
    SELECT T2.category
    FROM dbo.Temp AS T2
    WHERE T2.category IS NOT NULL
    GROUP BY T2.category
) AS T;

IF @Columns IS NOT NULL
BEGIN
    SET @Query = 
        N'SELECT [date], ' + 
        @Columns + 
        N' 
        FROM
        (
            SELECT [date], amount, category
            FROM dbo.Temp
        ) AS S
        PIVOT
        (
            MAX(amount)
            FOR category IN (' +
            @Columns +
            N')
        ) AS P;';

    EXECUTE sys.sp_executesql @Query;
END;

执行计划

结果

下面的代码提供了将输出中的NULL替换为0的结果。

表创建和数据插入:

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)

查询以生成准确的结果，并将NULL替换为零:

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

输出:

这就是我的解决方案，清除不必要的空值

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