动态 SQL Server 2005 枢轴 [英] Dynamic SQL Server 2005 Pivot
问题描述
我已经研究出如何在 SQL Server 2005 中使用 PIVOT 从表中透视一行,但是,我不喜欢这种方法,因为我不得不对列(货币代码)进行硬编码,并且我想要 Pivot动态选择列.
例如,假设您有下表(称为 OrderCash):
<块引用>OrderID CAD CHF EUR GBP JPY NOK USD40 0 0 128.6 552.25 -9232 0 -476241 0 0 250.2 552.25 -9232 0 -476242 233.23 0 552.25 -9232 0 0 -4762
硬编码的 Pivot 语句是:
SELECT OrderID,CurrCode + 'GBP CURNCY' 作为股票代码,现金作为头寸从(选择订单 ID,加元,瑞士法郎,欧元,英镑,日元,不,美元从订单现金) p转轴(Cash FOR CurrCode IN(加元、瑞士法郎、欧元、英镑、日元、挪威克朗、美元)) 作为 unpvt现金在哪里!= 0订单 ID = 42
这将返回以下必需的表:
OrderID 代码位置42 CADGBP 货币 233.2342 欧元货币 552.2542 GBPGBP 货币 -923242 美元英镑货币 -4762
当有人告诉我需要将澳元作为表中的新货币时,问题又出现了?
仅供参考,我有一个表值函数,它将所有列名作为表返回:
ALTER FUNCTION [dbo].[GetTableColumnNames](@TableName NVARCHAR(250),@StartFromColumnNum INT)返回@ReturnTable 表(列名 NVARCHAR(250))作为开始插入@ReturnTable从 information_schema.columns 中选择 COLUMN_NAMEWHERE TABLE_NAME = @TableNameAND ORDINAL_POSITION >= @StartFromColumnNum按 ORDINAL_POSITION 排序返回结尾
所以简单的部分已经完成 (SELECT * FROM dbo.GetTableColumnNames('OrderCash',2)),我遇到的问题是将带有列名的这个动态"表插入到数据透视表中?
任何帮助将不胜感激.非常感谢伯蒂.
我最近做了太多这样的动态查询......(我的专栏按客户逐月移动).这是一种方法——不测试,不调试,可能有一些错误需要解决:
DECLARE@Command nvarchar(max),@ColumnList nvarchar(max),@OrderId 整数,@调试位-- 建立一个以逗号分隔的列列表SELECT @ColumnList = isnull(@ColumnLIst + ',', , '') + ColName从 dbo.GetTableColumnNames('OrderCash', 2)-- 在查询中的两个位置插入列列表SET @Command = 替换('选择订单 ID,CurrCode + ‘‘GBP CURNCY’‘ 作为股票代码,现金作为头寸从(选择订单 ID,<@ColumnList>从订单现金) p转轴(Cash FOR CurrCode IN(<@ColumnList>)) 作为 unpvt现金在哪里!= 0和 OrderID = @OrderId', '<@ColumnList>', @ColumnList)- 总是包括这样的东西!如果@Debug = 1打印@命令-- 在 EXECUTE (@Command) 上使用 sp_executeSQL 允许您执行-- 通过参数传递计划重用(这是您可能需要的部分-- 稍微调试一下,但它会起作用)EXECUTE sp_executeSQL @Command, N'@OrderId int', @OrderId
I have worked out how to pivot a row from a table using PIVOT in SQL Server 2005, however, I dont like the method as I have had to hard code the columns (Currency Codes) and I would like to have the Pivot select the columns dynamically.
As an Example, imagine you have the following table (called 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
The hard-coded Pivot statement is:
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
This would return the following required table:
OrderID Ticker Position
42 CADGBP CURNCY 233.23
42 EURGBP CURNCY 552.25
42 GBPGBP CURNCY -9232
42 USDGBP CURNCY -4762
The problem arrises further down the road when someone tells me I need to have AUD as a new currency in the table?
FYI, I have a table-valued function that returns all the column names as a table:
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
So the easy bit has been done (SELECT * FROM dbo.GetTableColumnNames('OrderCash',2)), the problem I am having is inserting this 'dynamic' table with the column names into the Pivot?
Any help would be much appreciated. Many thanks Bertie.
I've done a few too many of these dynamic queries of late... (my columns shift by client by month). Here's one way to do it--no testing, no debugging, there might be a few bugs to iron out:
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
这篇关于动态 SQL Server 2005 枢轴的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!