SQL Server 中具有动态列的数据透视表 [英] Pivots with dynamic columns in SQL Server

问题描述

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

I am working on an SQL Query using pvots with dynamic columns in SQL Server (T-sql). Rather than submitting my lengthy query, I’m illustrating my problem with a simplified model.

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

I create 2 tables: Table1 and Table2 and populate them with a few entries as follows:

表1:

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

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

1.......................11 月 11 日

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

2.......................2 月 11 日

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

3.......................3 月 11 日

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

表2:

Col_ID2......Account......AccountName......金额

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

1......................121............电力............10000

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

2......................121............电力............20000

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

3.............121............电力............30000

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

1.............122............电话............100

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

2.............122............电话............200

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

3.............122............电话............300

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

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

I am creating a Pivot, but I want the column names to be generated parametrically (based on dates keyed in from the input screen), and not hard coded.

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

The Query below works well, but gives only a few columns as foll:

11 年 1 月…………11 年 2 月…………11 年 3 月

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

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

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

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

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

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

I want the query to return the descriptive columns also, as foll:

帐户............帐户名称............ Jan-11............Feb-11...............3-11

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

121........电力......................10,000.00......20,000.00.............30,000.00

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

122.......电话............100.00...........200.00.............300.00

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

谁能帮我修改我的查询以实现我的目标?

Could anybody please help me modify my query so that I can achieve my objective?

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

This query is an adaptation of the following article written by Dr. Andras in Sept 2007. http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

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

Somebody remarked that the code could be subject to Injection Attacks and proposed to use Quotename function instead of concatenating square brackets.

您能否解释一下如何在我的查询中使用 Quotename.

Could you explain how to use Quotename in my query.

非常感谢，

莱昂.
.
.

Leon Lai .
.
.

这是我的查询:

------------------------ 创建 &填充 table1 --------------------------------

------------------------ create & populate 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') 

--------------创建 &填充 table2 ----------------------------------

-------------------------create & populate table2 ----------------------------------

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)   

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

---------------------------------- create columns headings -------------------

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 -----------------------

------------------------------------- create @query ----------------------

DECLARE @query NVARCHAR(4000)   

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

FROM   

--------------子查询-----

--------------------------subquery-----

<代码>(选择
t1.Col_Name,
t2.帐户，
t2.金额
从表 1 作为 t1
加入表 2 作为 t2 ON t1.Col_ID1 = t2.Col_ID2
) p

------枢轴-------------

--------------------pivot -------------------------

<代码>PIVOT
(
总和([金额])
FOR Col_Name IN
('+
@cols +' )
) 作为列兵'

----------执行 &滴----------

----------------------exec & drop----------

EXECUTE(@query)
drop table table1
drop table table2

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

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

菲利普，

非常感谢您的回复.

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

Your proposed query works smoothly, and generates the expected screen, but it is not exactly what I wanted.

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

First, thanks for the code: SELECT @cols = isnull (@cols + ',' , ' ' ) + '[' + Col_Name + ']'

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

It is simpler and does replace my line involving stuff and xml path, apparently with same effect.

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

Let me explain what I want to do.

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

I want to develop a query in Sap Business 1 (An Accounting Package - or call it an ERP). Sap uses T-sql in Microsoft Server 2008, and has its own query generator. With very few exceptions, Sap sql is similar to T-sql.

我希望我的查询列出 12 个月内每月的所有收入和支出.

I want my query to give a list of all income and expenses month by month over a 12-month period.

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

However, I don't want my column headings to be hardcoded, (as this would require me to amend my query from time to time) as follows:

11 月 11 日、2 月 11 日、3 月 11 日、4 月 11 日、..... 12 月 11 日

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

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

Rather, I want the column headings to be Dynamically generated from the dates which the user enters in the input screen.

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

As I mentioned, the query which I posted on the forum is an over-simplified version of my real query, used only for illustration. The real query contains several variables and an input screen (called Query - Selection Criteria box in Sap b1) permits the user to enter a date. It is this date which will be used to determine the column names dynamically.

这就是我需要@cols、@query、pivot 等复杂工具的原因.

This is why I needed such complicated tools as @cols, @query, pivot etc.

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

If I input, say '01.06.11' (01 Jun 2011) in the input screen, this date will be passed on to the sql which will determine the names of the column headings as foll:

6 月 11 日、7 月 11 日、8 月 11 日..... 5 月 12 日.

Jun-11, Jul-11, Aug-11 ..... May-12.

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

If I input another date, say '01.09.10' (01 Sep 2010), the column headings will change to:

9 月 10 日、10 月 10 日、.... 8 月 11 日

Sep-10, Oct-10, .... Aug-11

看来你已经硬编码了我的列标题.

It seems that you have hardcoded my column headings.

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

Could you have a second look at my query, and propose something that will allow the column names to be generated parametrically instead of being hard coded?

谢谢

莱昂

推荐答案

添加这些列非常简单.最终查询将是

Adding those columns is very simple. The final query would be

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.将它们扔进构建语句中，你就完成了:

which has t2.AccountName added to the subquery, and Account and AccountName added to the initial SELECT. Toss them into the build statement and you're done:

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 注入，我能看到这种情况发生的唯一方法是，如果有人以某种方式在 Table1.Col_Name 中嵌入了恶意代码，如果您不得不担心这一点，那么您遇到的问题比锁定"这个动态查询更大.

As for SQL injection, the only way I can see that happening is if someone somehow embeds malicious code within Table1.Col_Name, and if you have to worry about that, you have bigger problems than "locking down" this dynamic query.

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

Also worth mentioning, I'd use the following to build the list of columns (@Cols) because its shorter and easier to read, but mostly because I don't like XML.

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

这篇关于SQL Server 中具有动态列的数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！