如何在存储过程查询中定义新列 [英] How to define a new column in a stored procedure query

问题描述

我有一个实现数据透视表的存储过程.我的 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

这篇关于如何在存储过程查询中定义新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！