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

问题描述

我正在使用SQL Server(T-sql)中带有动态列的pvots进行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 ............................. 1月11日

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

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

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

3 ............................. 11年3月

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

表2:

Col_ID2 ......帐户.....帐户名称......金额

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

我正在创建数据透视表，但我希望参数化生成列名(基于从输入屏幕键入的日期)，而不是硬编码.

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日........... 2月11日........ 3月11日

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:

帐户..............帐户名称.............. 1月11日............ 2月11日..... .........三月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/blogs/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-----

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

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

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

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

---------------------- exec&放下----------

----------------------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 = isull(@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业务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月1日，11月2日，11月3日，11月4日..... 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将确定列标题的名称为foll:

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:

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

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:

10月9日，10月10日.... 11月8日

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.将它们放入build语句中，您就可以完成:

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