如何在存储过程查询中定义新列

本文介绍了如何在存储过程查询中定义新列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个实现数据透视表的存储过程.我的 Select 语句是在变量 @query 中定义的.

I have a stored procedure which realizes a pivot table. My Select statement was defined in a variable @query.

代码如下:

BEGIN SET NOCOUNT ON; DECLARE @colNo nvarchar(max) DECLARE @query nvarchar(max) SET NOCOUNT ON; WITH vals AS ( SELECT DISTINCT t.No FROM QR_Tests t ) SELECT @colNo = COALESCE(@colNo + ', ', '') + '['+ No +']' FROM vals ORDER BY No SET @query = 'SELECT * FROM ( SELECT CASE WHEN GROUPING(No) = 0 THEN CAST(No as CHAR(12)) ELSE [ALL] END As No, CASE WHEN GROUPING(quote) = 0 THEN CAST(quote as CHAR(7)) ELSE [ALL] END As Quote FROM QRTestView WHERE datum >= @from_val and datum <= @to_val GROUP BY No, Quote WITH CUBE) AS sel PIVOT ( COUNT(Quote) FOR No IN ('+ @colNo +', [ALL]) ) AS p' EXEC sp_executesql @query, N'@from_val datetime, @to_val datetime', @from_val = @from, @to_val = @to END

我实际上需要以下结果:

I need the following result practically:

| Quote | DE10101 | DE10121 | DE22034 | ... | ALL | 100 | 2 | 0 | 3 | ... | 5 | 99 | 0 | 4 | 3 | ... | 7 | 98 | 5 | 1 | 7 | ... | 13 | 90 | 0 | 0 | 1 | ... | 1 | 50 | 12 | 10 | 4 | ... | 26 | ALL | 19 | 15 | 18 | ... | 52

我尝试了这个 博客.当我执行存储过程时，我收到错误消息:

I tried an example from this blog. When I execute the stored procedure, then I'm getting the error message:

无效的列名ALL".

如何为我的结果定义这个新列?

How can I define this new column for my result?

更新:要了解转换，这里是源表:

UPDATE: To understand the transformation, here the source table:

| No | Quote | Datum | DE10101 | 100 | 2016-01-01 | DE10121 | 100 | 2016-01-02 | DE10101 | 100 | 2016-01-05 | DE22034 | 98 | 2016-01-05 | DE10101 | 98 | 2016-01-10 | DE10121 | 80 | 2016-01-10 | DE22034 | 98 | 2016-01-10 | DE22034 | 80 | 2016-01-11 | DE10101 | 100 | 2016-01-20 | DE10121 | 80 | 2016-01-21

这应该是结果:

| Quote | DE10101 | DE10121 | DE22034 | ALL | 100 | 3 | 1 | 0 | 4 | 98 | 1 | 0 | 2 | 3 | 80 | 0 | 2 | 1 | 3 | ALL | 4 | 3 | 3 | 10

目前存储过程不计算每个No的引号数量.

Currently the stored procedure doesn't count the amount of quotes for every No.

推荐答案

据我所知 ALL 是该行中所有值的总和.所以你需要重写你的 SP 代码(根据你提供的数据):

As I understand ALL is the sum of all values in this row. So you need to rewrite your SP code (based on data you provided):

BEGIN SET NOCOUNT ON; DECLARE @colNo nvarchar(max) DECLARE @colSum nvarchar(max) -- this will store [Column1]+[Column2] etc DECLARE @query nvarchar(max) SET NOCOUNT ON; WITH vals AS ( SELECT DISTINCT t.No FROM QR_Tests t ) SELECT @colNo = COALESCE(@colNo + ', ', '') + QUOTENAME([No]), @colSum = COALESCE(@colSum + '+ ', '') + QUOTENAME([No]) FROM vals ORDER BY [No] -- add this column here SET @query = 'SELECT * FROM ( SELECT *, '+@colSum+' as [ALL] FROM ( SELECT CAST([No] as CHAR(12)) As No, CAST(quote as CHAR(7)) As Quote, CAST(quote as CHAR(7)) As Q FROM QRTestView WHERE datum >= @from_val and datum <= @to_val ) AS sel PIVOT ( COUNT(Q) FOR No IN ('+ @colNo +') ) AS p UNION ALL SELECT ''ALL'',*, '+@colSum+'[ALL] FROM ( SELECT CAST([No] as CHAR(12)) As No, COUNT(CAST(quote as CHAR(7))) As Quote, FROM QRTestView WHERE datum >= @from_val and datum <= @to_val GROUP BY CAST([No] as CHAR(12)) ) AS sel PIVOT ( MAX(Quote) FOR [No] IN ('+ @colNo +') ) AS p ) as d ORDER BY CASE WHEN Quote = ''ALL'' THEN 0 ELSE CAST(Quote as int) END DESC' EXEC sp_executesql @query, N'@from_val datetime, @to_val datetime', @from_val END