×

Loading...
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

Using pivot operator, creating Column Headers is a challenge. Dynamic query can be a solution.

Following T-SQL sample shows how to use dynamic query in a pivot operator:

Normal query:

SELECT Name, Max(TaxRate) TaxRate from Sales.SalesTaxRate GROUP BY Name

Pivot query:

DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + + ',[' + Name + ']', '[' + Name+ ']') FROM Sales.SalesTaxRate GROUP BY Name

EXEC (’SELECT * FROM (SELECT NAME, TaxRate FROM Sales.SalesTaxRate) AS A PIVOT( MAX(TaxRate) FOR Name IN (‘ + @PivotColumnHeaders + ‘)) AS B’)

HTH
Report

Replies, comments and Discussions:

  • 工作学习 / 学科技术讨论 / An Oracle SQL Query needs to show pivot table information. Column names in SELECT statement are actually stored in a param table. Is it possible to build dynamic columns in SQL Select statement? Thanks!
    I found one reference in the following link, looks like this is impossible for a single SQL query....?

    • Using pivot operator, creating Column Headers is a challenge. Dynamic query can be a solution.
      Following T-SQL sample shows how to use dynamic query in a pivot operator:

      Normal query:

      SELECT Name, Max(TaxRate) TaxRate from Sales.SalesTaxRate GROUP BY Name

      Pivot query:

      DECLARE @PivotColumnHeaders VARCHAR(MAX)
      SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + + ',[' + Name + ']', '[' + Name+ ']') FROM Sales.SalesTaxRate GROUP BY Name

      EXEC (’SELECT * FROM (SELECT NAME, TaxRate FROM Sales.SalesTaxRate) AS A PIVOT( MAX(TaxRate) FOR Name IN (‘ + @PivotColumnHeaders + ‘)) AS B’)

      HTH
      • 谢谢. 其实不仅是要做pivot table, 还要求在multiple values 里面,只选出一个来显示. 或者concat multiple values together 也行. ... 看来一个SQL query 是无法实现的了. 不明白可以有max, min 对数字,为什么不能有concat对string..?