This website requires Javascript to function properly. Please go to the setting of your web browser and enable Javascript for this website.

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.

deep_blue(BLUE)

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

(#5464318@0)
Last Updated: 2009-8-5
This post has been archived. It cannot be replied.

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! -nicetomeetyou(蓦然回首彩虹跨天地); 2009-7-30 {102} (#5454506@0)
I found one reference in the following link, looks like this is impossible for a single SQL query....?

http://www.praetoriate.com/t_pivot_sql.htm

顶 -nicetomeetyou(平淡是真); 2009-8-4 (#5462857@0)
Using pivot operator, creating Column Headers is a challenge. Dynamic query can be a solution. -deep_blue(BLUE); 2009-8-5 {532} (#5464318@0)
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..? -nicetomeetyou(平淡是真); 2009-8-5 (#5465091@0)

@Florida

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

Replies, comments and Discussions:

More Topics