没有数据没有经过测试,但就这个意思,in中的内容通过字符串拼接出来,然后在拼接完SQL,最后执行,你自己调试一下 DECLARE @cols AS NVARCHAR(MAX),@query AS NVARCHAR(MAX) select @cols =STUFF((SELECT DISTINCT ','+ QUOTENAME([subject]) from n881820_students_score WHERE stuid = 10002 FOR XML PATH(''...
TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); SET @query = 'SELECT ProductID, ' + @cols + ' FROM ( SELECT ProductID, SaleDate, Quantity FROM Sales ) AS SourceTable PIVOT ( SUM(Quantity) FOR SaleDate IN (' + @cols + ') ) AS PivotTable;'; EXEC sp_executesql @query;...
-- unpivot 语法 SELECT [columns not unpivoted], [unpivot_column], [value_column], FROM () AS <alias for the source data> UNPIVOT ( [value_column] FOR [unpivot_column] IN ( <column_list> ) ) AS <alias for unpivot> Where: --[columns not unpivoted]: 没有被转换的列名。 --[unpivo...
以下示例将检索驻留在计划缓存中的所有查询计划的快照,方法是通过查询sys.dm_exec_cached_plans动态管理视图来检索缓存中所有查询计划的计划句柄。 然后,指定CROSS APPLY运算符以将计划句柄传递给sys.dm_exec_query_plan。 当前在计划缓存中的每个计划的 XML 显示计划输出位于返回的表的query_plan列中。
AS <alias for the source query> PIVOT ( <aggregation function>(<column being aggregated>) FOR [<column that contains the values that will become column headers>] IN ( [first pivoted column], [second pivoted column], ... [last pivoted column]) ...
pivot (max(remark)for[key]in([name],[phone],[address]) )aspv wheretable_id=1andtable_name='Applications' ) s 然后观察规律,将固定格式的语句,变成动态拼接的sql语句,最后通过 exec(@sql) 获取结果: declare@eventnvarchar(max)=''declare@sqlnvarchar(max)=''declare@queryexpnvarchar(max)=''--获...
> SELECT deptnoFROM empPIVOT(SUM(sal1)FOR deptnoIN(10, 20, 30, 40)); 217: Column (deptno) not found in any table in the query (or SLV is undefined).Error in line 2Near character position 0 同样也不能查询任何出现在 pivot_clause 中的列。比如试图查询 sal1 列，也会引发了同样的...
syntaxsql SELECT[<non-pivoted column>[AS<column name>] , ] ... [[AS<column name>] , ] [<new output column created for values in result of the source query>[AS<column name>] ]FROM(<SELECT query that produces the data>)AS<alias for the source query>UNPIVOT(<new output column creat...
syntaxsql SELECT[<non-pivoted column>[AS<column name>] , ] ... [[AS<column name>] , ] [<new output column created for values in result of the source query>[AS<column name>] ]FROM(<SELECT query that produces the data>)AS<alias for the source query>UNPIVOT(<new output column creat...
The short story on how it works using the last query. select * from DailyIncome -- Colums to pivot pivot ( max (IncomeAmount) -- Pivot on this column for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) -- Make colum where IncomeDay is in one of these. ...