动态SQL Server 2005透视
问题描述:
我已经研究出如何在SQL Server 2005中使用PIVOT从表中旋转一行,但是,我不喜欢该方法,因为我必须对列(货币代码)进行硬编码,而且我会喜欢让Pivot动态选择列。动态SQL Server 2005透视
举个例子,假设你有如下表(称为OrderCash):
OrderID CAD CHF EUR GBP JPY NOK USD 40 0 0 128.6 552.25 -9232 0 -4762 41 0 0 250.2 552.25 -9232 0 -4762 42 233.23 0 552.25 -9232 0 0 -4762
硬编码枢纽的说法是:
SELECT OrderID,
CurrCode + 'GBP CURNCY' AS Ticker,
Cash AS Position
FROM
(
SELECT OrderID,
CAD,
CHF,
EUR,
GBP,
JPY,
NOK,
USD
FROM OrderCash
) p
UNPIVOT
(
Cash FOR CurrCode IN
(CAD, CHF, EUR, GBP, JPY, NOK, USD)
) AS unpvt
WHERE Cash != 0
And OrderID = 42
这将返回以下所需表格:
OrderID Ticker Position
42 CADGBP CURNCY 233.23
42 EURGBP CURNCY 552.25
42 GBPGBP CURNCY -9232
42 USDGBP CURNCY -4762
当有人告诉我我需要将澳元作为表中的新货币时,问题会进一步出现在路上吗?
仅供参考,我有一个返回的所有列名作为表的表值函数:
ALTER FUNCTION [dbo].[GetTableColumnNames]
(
@TableName NVARCHAR(250),
@StartFromColumnNum INT
)
RETURNS @ReturnTable TABLE
(
ColName NVARCHAR(250)
)
AS
BEGIN
INSERT INTO @ReturnTable
SELECT COLUMN_NAME from information_schema.columns
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION >= @StartFromColumnNum
ORDER BY ORDINAL_POSITION
RETURN
END
所以容易位已经完成(SELECT * FROM dbo.GetTableColumnNames(“OrderCash”,2 )),我遇到的问题是将这个'动态'表的列名插入到Pivot中?
任何帮助将不胜感激。 非常感谢 Bertie。
答
我已经完成了很多这些动态查询后期...(我的列按月移动客户端)。下面是做这件事 - 没有测试,无需调试,可能会有一些错误,以消除:
DECLARE
@Command nvarchar(max)
,@ColumnList nvarchar(max)
,@OrderId int
,@Debug bit
-- Build a comman-delimited list of the columns
SELECT @ColumnList = isnull(@ColumnLIst + ',', , '') + ColName
from dbo.GetTableColumnNames('OrderCash', 2)
-- Insert the list of columns in two places in your query
SET @Command = replace('
SELECT OrderID,
CurrCode + ‘‘GBP CURNCY’‘ AS Ticker,
Cash AS Position
FROM
(
SELECT OrderID, <@ColumnList>
FROM OrderCash
) p
UNPIVOT
(
Cash FOR CurrCode IN
(<@ColumnList>)
) AS unpvt
WHERE Cash != 0
And OrderID = @OrderId
', '<@ColumnList>', @ColumnList)
-- Always include something like this!
IF @Debug = 1
PRINT @Command
-- Using sp_executeSQL over EXECUTE (@Command) allows you execution
-- plan resuse with parameter passing (this is the part you may need
-- to debug on a bit, but it will work)
EXECUTE sp_executeSQL @Command, N'@OrderId int', @OrderId
我会折腾出(典型和刺激性)实用主义者响应:汇率打交道时和外汇,你可能会更多,更好的数据正常化。似乎更有可能的是,有人总是希望将“多一种货币”添加到您的列表中。 –