没有数据没有经过测试,但就这个意思,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...
适用于:SQL Server 2016 (13.x) 及更高版本和 SQL 数据库。 返回一个表,其中包含在 CONTAINED IN 参数的两个日期时间值定义的时间范围内打开和关闭的所有记录版本的值。 正好在下限时间激活的记录,或者在上限时间停止活动的行将包括在内。 都 返回具有当前表和历史记录表中所有行中的值的表。
The query: SELECT Student, [English], [Mathematics], [Science], [Programming], [History] FROM ( SELECT Grades, Subject FROM Grade_Report )AS SourceTable PIVOT ( AVG(Grades) FOR Subject IN([English], [Mathematics], [Science], [Programming], [History]) ...
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]) ...
13 where = where.And((l, u, s) => SM.In(u.Id, arruser)); 14 15 DapperSqlMaker<LockPers, Users, SynNote> query = LockDapperUtilsqlite<LockPers, Users, SynNote> 16 .Selec() 17 .Column((lp, u, s) => //null) //查询所有字段 ...
> 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...