blocks|key|582345|text|您可以使用动态TSQL来实现这一点(记住使用QUOTENAME来避免SQL注入攻击)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|582346|Pivots+with+Dynamic+Columns+in+SQL+Server+2005|offset|length|582347|SQL+Server+-+Dynamic+PIVOT+Table+-+SQL+Injection|582348|对+The+Curse+and+Blessings+of+Dynamic+SQL的强制引用|582349|entityMap|0|LINK|mutability|MUTABLE|url|http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx|1|https://stackoverflow.com/questions/1439403/sql-server-dynamic-pivot-table-sql-injection|2|http://www.sommarskog.se/dynamic_sql.html^0|0|0|1A|0|0|0|1C|1|0|1|13|2|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|W|8|@]|9|@$D|X|E|Y|1|Z]]|A|$]]|$1|F|3|G|5|6|7|10|8|@]|9|@$D|11|E|12|1|13]]|A|$]]|$1|H|3|I|5|6|7|14|8|@]|9|@$D|15|E|16|1|17]]|A|$]]|$1|J|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|K|$L|$5|M|N|O|A|$P|Q]]|R|$5|M|N|O|A|$P|S]]|T|$5|M|N|O|A|$P|U]]]]

You can achieve this using dynamic TSQL (remember to use QUOTENAME to avoid SQL injection attacks):

<a href="http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx" rel="nofollow noreferrer">Pivots with Dynamic Columns in SQL Server 2005</a>

<a href="https://stackoverflow.com/questions/1439403/sql-server-dynamic-pivot-table-sql-injection">SQL Server - Dynamic PIVOT Table - SQL Injection</a>

Obligatory reference to<a href="http://www.sommarskog.se/dynamic_sql.html" rel="nofollow noreferrer"> The Curse and Blessings of Dynamic SQL</a>

blocks|key|582373|text|动态SQL+PIVOT：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|582374|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+','+%2B+QUOTENAME(c.category)+
++++++++++++FROM+temp+c
++++++++++++FOR+XML+PATH(''),+TYPE
++++++++++++).value('.',+'NVARCHAR(MAX)')+
++++++++,1,1,'')

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


execute(@query)

drop+table+temp|code-block|syntax|javascript|582375|结果：|582376|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|582377|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Dynamic SQL PIVOT:

<pre><code>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
</code></pre>

Results:

<pre><code>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
</code></pre>

blocks|key|366598|text|这是我的解决方案，清除不必要的空值|type|unstyled|depth|inlineStyleRanges|entityRanges|data|366599|DECLARE+@cols+AS+NVARCHAR(MAX),
@maxcols+AS+NVARCHAR(MAX),
@query++AS+NVARCHAR(MAX)

select+@cols+=+STUFF((SELECT+','+%2B+QUOTENAME(CodigoFormaPago)+
++++++++++++++++from+PO_FormasPago
++++++++++++++++order+by+CodigoFormaPago
++++++++FOR+XML+PATH(''),+TYPE
++++++++).value('.',+'NVARCHAR(MAX)')+
++++,1,1,'')

select+@maxcols+=+STUFF((SELECT+',MAX('+%2B+QUOTENAME(CodigoFormaPago)+%2B+')+as+'+%2B+QUOTENAME(CodigoFormaPago)
++++++++++++++++from+PO_FormasPago
++++++++++++++++order+by+CodigoFormaPago
++++++++FOR+XML+PATH(''),+TYPE
++++++++).value('.',+'NVARCHAR(MAX)')
++++,1,1,'')

set+@query+=+'SELECT+CodigoProducto,+DenominacionProducto,+'+%2B+@maxcols+%2B+'
++++++++++++FROM
++++++++++++(
++++++++++++++++SELECT+
++++++++++++++++CodigoProducto,+DenominacionProducto,
++++++++++++++++'+%2B+@cols+%2B+'+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+('+%2B+@cols+%2B+')
++++++++++++++++)+p+
++++++++++++)++xx+
++++++++++++GROUP+BY+CodigoProducto,+DenominacionProducto'

t+@query;

execute(@query);|code-block|syntax|javascript|366600|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

There's my solution cleaning up the unnecesary null values

<pre><code>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);
</code></pre>

blocks|key|366651|text|动态SQL+PIVOT|type|unstyled|depth|inlineStyleRanges|entityRanges|data|366652|创建列字符串的不同方法|366653|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+%2B+QUOTENAME(category)+%2B+','+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+('+%2B+@cols+%2B+')
)+piv'

execute(@query)
drop+table+#temp|code-block|syntax|javascript|366654|结果|366655|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|366656|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

<h2>Dynamic SQL PIVOT</h2>

Different approach for creating columns string

<pre><code>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
</code></pre>

Result

<pre><code>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
</code></pre>

blocks|key|582584|text|我知道这个问题比较老，但我仔细研究了答案，并认为我也许能够扩展这个问题的“动态”部分，并可能帮助某人解决这个问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|582585|首先，也是最重要的，我构建这个解决方案是为了解决几个同事遇到的一个问题，即需要快速旋转的变化无常的大型数据集。|582586|这个解决方案需要创建一个存储过程，所以如果这不能满足您的需求，请现在停止阅读。|582587|此过程将接收pivot语句的关键变量，以便为不同的表、列名和聚合动态创建pivot语句。静态列用作透视表的group+by+/+identity列(如果没有必要，可以将其从代码中剥离出来，但在透视表语句中非常常见，并且对于解决原始问题是必需的)，透视表列是生成最终结果列名的位置，值列是应用聚合的位置。表参数是表的名称，包括模式(schema.tablename)，这部分代码可能需要一些改进，因为它并不像我希望的那样干净。它对我来说很有效，因为我的使用不是公开的，sql注入也不是问题。aggregate参数将接受任何标准sql+Aggregate+'AVG'，'SUM'，'+MAX‘等。代码也默认为max作为聚合。这不是必需的，但最初为其构建的受众不理解pivots，并且通常使用max作为聚合。|582588|让我们从创建存储过程的代码开始。这段代码应该可以在SSMS+2005和更高版本的所有版本中工作，但我没有在2005或2016年测试过它，但我看不出为什么它不能工作。|582589|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+'',+''+%2B+CAST(''[''%2BCONVERT(VARCHAR,'%2B+@PIVOT_COLUMN%2B')%2B'']''++AS+VARCHAR(50))+[text()]
++++++++++++++++++++++++++++FROM+'%2B@TABLE%2B'
++++++++++++++++++++++++++++WHERE+ISNULL('%2B@PIVOT_COLUMN%2B','''')+<>+''''
++++++++++++++++++++++++++++FOR+XML+PATH(''''),+TYPE)
++++++++++++++++++++++++++++.value(''.'',''NVARCHAR(MAX)''),1,2,''+'')+as+PIVOT_VALUES
++++++++++++++++++++++++++++from+'%2B@TABLE%2B'+ma
++++++++++++++++++++++++++++ORDER+BY+'+%2B+@PIVOT_COLUMN+%2B+''

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,')+%2B+'+nvarchar(255)+null')


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

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

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

++++++++++++++++++++SELECT+*+FROM+@RETURN_TABLE'



EXEC+SP_EXECUTESQL+@SQLSTRING

END|code-block|syntax|javascript|582590|接下来，我们将为示例准备好数据。我采用了已接受答案中的数据示例，并在此概念证明中添加了几个数据元素，以显示聚合更改的不同输出。|582591|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)|582592|下面的示例显示了不同的执行语句，并以一个简单的示例显示了不同的聚合。我没有选择更改静态、透视和值列来保持示例的简单性。您应该能够只复制和粘贴代码来开始自己处理它|582593|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'|582594|此执行将分别返回以下数据集。|582595|​|582596|📷|atomic|offset|length|582597|582598|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/c5afb18038eb7ad38690ba107065d4ac.png|imageAlt^0|0|0|0|0|0|0|0|0|0|0|0|0|0|1|0|0|0^^$0|@$1|2|3|4|5|6|7|1F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1G|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|1H|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|1I|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|1J|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|1K|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|1L|8|@]|9|@]|A|$]]|$1|Q|3|R|5|L|7|1M|8|@]|9|@]|A|$M|N]]|$1|S|3|T|5|6|7|1N|8|@]|9|@]|A|$]]|$1|U|3|V|5|L|7|1O|8|@]|9|@]|A|$M|N]]|$1|W|3|X|5|6|7|1P|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|10|3|11|5|12|7|1R|8|@]|9|@$13|1S|14|1T|1|1U]]|A|$]]|$1|15|3|Z|5|6|7|1V|8|@]|9|@]|A|$]]|$1|16|3|-4|5|6|7|1W|8|@]|9|@]|A|$]]]|17|$18|$5|19|1A|1B|A|$1C|1D|1E|-4]]]]

I know this question is older but I was looking thru the answers and thought that I might be able to expand on the "dynamic" portion of the problem and possibly help someone out.

First and foremost I built this solution to solve a problem a couple of coworkers were having with inconstant and large data sets needing to be pivoted quickly.

This solution requires the creation of a stored procedure so if that is out of the question for your needs please stop reading now. 

This procedure is going to take in the key variables of a pivot statement to dynamically create pivot statements for varying tables, column names and aggregates. The Static column is used as the group by / identity column for the pivot(this can be stripped out of the code if not necessary but is pretty common in pivot statements and was necessary to solve the original issue), the pivot column is where the end resultant column names will be generated from, and the value column is what the aggregate will be applied to. The Table parameter is the name of the table including the schema (schema.tablename) this portion of the code could use some love because it is not as clean as I would like it to be. It worked for me because my usage was not publicly facing and sql injection was not a concern. The Aggregate parameter will accept any standard sql aggregate 'AVG', 'SUM', 'MAX' etc. The code also defaults to MAX as an aggregate this is not necessary but the audience this was originally built for did not understand pivots and were typically using max as an aggregate.

Lets start with the code to create the stored procedure. This code should work in all versions of SSMS 2005 and above but I have not tested it in 2005 or 2016 but I can not see why it would not work.

<pre><code>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+','''') &lt;&gt; ''''
 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
</code></pre>

Next we will get our data ready for the example. I have taken the data example from the accepted answer with the addition of a couple of data elements to use in this proof of concept to show the varied outputs of the aggregate change.

<pre><code>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)
</code></pre>

The following examples show the varied execution statements showing the varied aggregates as a simple example. I did not opt to change the static, pivot, and value columns to keep the example simple. You should be able to just copy and paste the code to start messing with it yourself 

<pre><code>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'
</code></pre>

This execution returns the following data sets respectively.

<a href="https://i.stack.imgur.com/jY0zh.png" rel="noreferrer"><img src="https://i.stack.imgur.com/jY0zh.png" alt="enter image description here"></a>

blocks|key|366825|text|SQL+Server+2017更新版本，使用STRING_AGG函数构造透视表列列表：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|366826|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,+'+%2B+@cols+%2B+'+from+
++++++++++++(
++++++++++++++++select+date
++++++++++++++++++++,+amount
++++++++++++++++++++,+category
++++++++++++++++from+temp
+++++++++++)+x
++++++++++++pivot+
++++++++++++(
+++++++++++++++++max(amount)
++++++++++++++++for+category+in+('+%2B+@cols+%2B+')
++++++++++++)+p+';

execute(@query);

drop+table+temp;|code-block|syntax|javascript|366827|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Updated version for SQL Server 2017 using STRING_AGG function to construct the pivot column list:

<pre><code>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;
</code></pre>

blocks|key|366829|text|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+%2B+',','')+%2B+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,+'+%2B+@PivotColumns+%2B+'
++++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+('+%2B+@PivotColumns+%2B+')+
++++)AS+PVTTable'
+
--SELECT+++@SQLQuery
--Execute+dynamic+query
EXEC+sp_executesql+@SQLQuery


Drop+table+#PivotExample|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|366830|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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
</code></pre>

blocks|key|366863|text|完全通用的方式，可以在非传统的MS+SQL环境中工作(例如Azure+Synapse+Analytics无服务器SQL池)-它在SPROC中，但不需要这样使用……|type|unstyled|depth|inlineStyleRanges|entityRanges|data|366864|--+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;|code-block|syntax|javascript|366865|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Fully generic way that will work in non-traditional MS SQL environments (e.g. Azure Synapse Analytics Serverless SQL Pools) - it's in a SPROC but no need to use as such...
<pre><code>-- 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;
</code></pre>

I've been tasked with coming up with a means of translating the following data:

<pre><code>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
</code></pre>

into the following:

<pre><code>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
</code></pre>

The blank spots can be NULLs or blanks, either is fine, and the categories would need to be dynamic. Another possible caveat to this is that we'll be running the query in a limited capacity, which means temp tables are out. I've tried to research and have landed on <code>PIVOT</code> but as I've never used that before I really don't understand it, despite my best efforts to figure it out. Can anyone point me in the right direction?

SQL Server dynamic PIVOT query?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我的任务是想出一种方法来翻译以下数据：date        category        amount1/1/2012    ABC             1000.002/1/2012    DEF             500.002/1/2012    GHI             800.002/10...

问SQL Server dynamic PIVOT查询？
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server dynamic PIVOT查询？EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server dynamic PIVOT查询？
EN