动态枢轴查询 [英] Dynamic Pivot Query
本文介绍了动态枢轴查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
以下查询执行良好...
The following query is executed well...
select LIC,PF from (select * from tbl_DeductionHead )up
pivot(sum(DeductionPerAmount) for DeductionHead In (LIC,PF)) as pvt
输出看起来像,
LIC PF
900.00 NULL
NULL 600.00
但我的疑问是如何获得列的动态字段值..
But my doubt is how can i get the dynamic Field Values of Column..
也就是说,
select * from (select * from tbl_DeductionHead )up
pivot(sum(DeductionPerAmount) for DeductionHead In (*)) as pvt
如何使用上述查询
推荐答案
要获得 PIVOT 动态,您必须编写一个存储过程.
To have the PIVOT dynamic you have to write a stored procedure.
CREATE PROCEDURE dynPivot
(
@select varchar(255),
@pivotColumns varchar(255),
@summaries varchar(100)
) AS
DECLARE @pivot varchar(max), @sql varchar(max), @selectForPivotColumns varchar(max)
SELECT @selectForPivotColumns = REPLACE(@select, 'SELECT', 'SELECT ' + @pivotColumns + ' AS rowValuesToColumns, ')
CREATE TABLE #pivot_columns (pivot_column varchar(100))
SELECT @sql = 'SELECT DISTINCT rowValuesToColumns FROM (' + @selectForPivotColumns + ') as t'
INSERT INTO #pivot_columns
EXEC(@sql)
SELECT @pivot = COALESCE(@pivot + ', ', '') + '[' + pivot_column + ']' FROM #pivot_columns
SELECT @sql =
'
SELECT * FROM
(
' + @select + '
) AS t
PIVOT
(
' + @summaries + ' FOR ' + @pivotColumns + ' IN (' + @pivot + ')
) AS p
'
EXEC(@sql)
那么你可以这样称呼它:
Then you can call it like this:
EXEC dynPivot 'SELECT * FROM tbl_DeductionHead', 'DeductionHead', 'SUM(DeductionPerAmount)';
这篇关于动态枢轴查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文