sql-server-2005 - SQL Server 中具有动态列的数据透视表

Question

我正在使用带有 SQL Server (T-sql) 中动态列的 pvots 处理 SQL 查询。
我没有提交冗长的查询，而是用一个简化的模型来说明我的问题。

我创建了 2 个表:Table1 和 Table2 并用如下几个条目填充它们:

表格1:

Col_ID1...............Col_Name

1.........................Jan-11

2.........................Feb-11

3.........................Mar-11

表2:

Col_ID2......Account.....AccountName......Amount

1...............121...........Electricity............10000

2...............121...........Electricity............20000

3...............121...........Electricity............30000

1...............122...........Telephone..............100

2...............122...........Telephone..............200

3...............122...........Telephone..............300

我正在创建一个 Pivot，但我希望以参数方式生成列名(基于从输入屏幕键入的日期)，而不是硬编码。

下面的查询运行良好，但仅提供如下几列:

Jan-11...........Feb-11...........Mar-11

10,000.00......20,000.00......30,000.00

100.00...............200.00...........300.00

我希望查询也返回描述性列，如下所示:

Account...........AccountName...........Jan-11............Feb-11..............Mar-11

121.................Electricity..................10,000.00......20,000.00..........30,000.00

122.................Telephone.....................100.00...........200.00.............300.00

有人可以帮我修改我的查询，以便实现我的目标吗？

此查询是对 Andras 博士于 2007 年 9 月撰写的以下文章的改编。
http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

有人评论说代码可能会受到注入(inject)攻击，并建议使用 Quotename 函数而不是连接方括号。

你能解释一下如何在我的查询中使用 Quotename 吗？

非常感谢，

莱昂
.
.
.

这是我的查询:

------------------------ 创建并填充 table1 ---------- ----------

CREATE TABLE Table1
(Col_ID1 INT, 
Col_Name varchar(10))  

INSERT INTO Table1 VALUES (1, 'Jan-11')  
INSERT INTO Table1 VALUES (2, 'Feb-11')  
INSERT INTO Table1 VALUES (3, 'Mar-11') 

-------------------------创建和填充表2 --------------------- -------------

CREATE TABLE Table2  
(Col_ID2 INT,  
Account varchar(10),  
AccountName varchar(20),  
Amount numeric(18,6))  

INSERT INTO Table2 VALUES (1, 121, 'Electricity', 10000)  
INSERT INTO Table2 VALUES (2, 121, 'Electricity', 20000)  
INSERT INTO Table2 VALUES (3, 121, 'Electricity', 30000)  
INSERT INTO Table2 VALUES (1, 122, 'Telephone', 100)        
INSERT INTO Table2 VALUES (2, 122, 'Telephone', 200)   
INSERT INTO Table2 VALUES (3, 122, 'Telephone', 300)   

---------------------------------- 创建列标题 ------------- ------

DECLARE @cols NVARCHAR(2000)   
SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT   
'],[' + t2.Col_Name   
FROM Table1 AS t2   
ORDER BY '],[' + t2.Col_Name 
FOR XML PATH('')   
), 1, 2, '') + ']'   

------------------------------------- 创建@query ---------- ------------

DECLARE @query NVARCHAR(4000)   

SET @query = N'SELECT '+   
@cols +' 

FROM   

--------------子查询-----
(SELECT

t1.Col_Name,

t2.Account,

t2.Amount

FROM Table1 AS t1

JOIN Table2 AS t2 ON t1.Col_ID1 = t2.Col_ID2

) p
--------------------枢轴 -------------
PIVOT

(

Sum ([Amount] )

FOR Col_Name IN

( '+

@cols +' )

) AS pvt '

----------执行和删除----------

EXECUTE(@query)
drop table table1
drop table table2

==================================================== =====

嗨菲利普，

非常感谢您的回复。

您提出的查询运行顺利，并生成了预期的屏幕，但这并不是我想要的。

首先，感谢代码:
SELECT @cols = isnull (@cols + ',' , ' ' ) + '[' + Col_Name + ']'

它更简单，并且确实替换了我涉及东西和 xml 路径的行，显然具有相同的效果。

让我解释一下我想做什么。

我想在 Sap Business 1(会计软件包 - 或称其为 ERP)中开发一个查询。
Sap 在 Microsoft Server 2008 中使用 T-sql，并拥有自己的查询生成器。
除了极少数异常(exception)，Sap sql 与 T-sql 类似。

我希望我的查询在 12 个月内逐月列出所有收入和支出。

但是，我不希望我的列标题被硬编码，(因为这需要我不时修改我的查询)如下:

Jan-11, Feb-11, Mar-11, Apr-11, ..... Dec-11

相反，我希望从用户在输入屏幕中输入的日期动态生成列标题。

正如我所提到的，我在论坛上发布的查询是我真实查询的过度简化版本，仅用于说明。真正的查询包含几个变量和一个输入屏幕(在 Sap b1 中称为查询 - 选择标准框)允许用户输入日期。正是这个日期将用于动态确定列名。

这就是为什么我需要@cols、@query、pivot 等复杂工具的原因。

如果我在输入屏幕中输入“01.06.11”(2011 年 6 月 1 日)，则该日期将传递给 sql，该 sql 将确定列标题的名称，如下所示:

6 月 11 日，7 月 11 日，8 月 11 日 ..... 5 月 12 日。

如果我输入另一个日期，例如“01.09.10”(2010 年 9 月 1 日)，列标题将更改为:

10 年 9 月，10 年 10 月，.... 11 年 8 月

看来您已经硬编码了我的列标题。

您能否再看看我的查询，并提出一些允许以参数方式生成列名而不是硬编码的方法？

谢谢

莱昂

Answer 1

添加这些列非常简单。最后的查询是

SELECT Account, AccountName, [Feb-11],[Jan-11],[Mar-11]   FROM   
(SELECT
t1.Col_Name,
t2.Account,
t2.AccountName,
t2.Amount
FROM Table1 AS t1
JOIN Table2 AS t2 ON t1.Col_ID1 = t2.Col_ID2
) p
PIVOT
(
Sum ([Amount] )
FOR Col_Name IN
( [Feb-11],[Jan-11],[Mar-11] )
) AS pvt 

将 t2.AccountName 添加到子查询中，并将 Account 和 AccountName 添加到初始 SELECT 中。将它们扔进构建语句中，你就完成了:

DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT Account, AccountName, ' +    @cols +'   FROM   

(SELECT
t1.Col_Name,
t2.Account,
t2.AccountName,
t2.Amount
FROM Table1 AS t1
JOIN Table2 AS t2 ON t1.Col_ID1 = t2.Col_ID2
) p

PIVOT
(
Sum ([Amount] )
FOR Col_Name IN
( '+
@cols +' )
) AS pvt ' 

至于 SQL 注入(inject)，我能看到这种情况发生的唯一方法是，如果有人以某种方式在 Table1.Col_Name 中嵌入了恶意代码，如果你不得不担心这一点，那么你遇到的问题比“锁定”这个动态查询更大。

另外值得一提的是，我将使用以下内容来构建列列表 (@Cols)，因为它更短且更易于阅读，但主要是因为我不喜欢 XML。

DECLARE @cols NVARCHAR(2000)    
SELECT @cols = isnull(@cols + ',', '') + '[' + Col_Name + ']'
 FROM Table1
 ORDER BY Col_Name