sql server - tipos - Cómo definir una nueva columna en una consulta de procedimiento almacenado

procedimiento almacenado sql server select

Question

Tengo un procedimiento almacenado que realiza una tabla dinámica. Mi declaración Select se definió en una variable @query .

Aquí está el código:

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

Necesito el siguiente resultado prácticamente:

| 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

Probé un ejemplo de este blog . Cuando ejecuto el procedimiento almacenado, aparece el mensaje de error:

nombre de columna inválido ''ALL''.

¿Cómo puedo definir esta nueva columna para mi resultado?

ACTUALIZACIÓN: Para comprender la transformación, aquí la tabla fuente:

| 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

Y este debería ser el resultado:

| 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   

Actualmente, el procedimiento almacenado no cuenta la cantidad de citas para cada No

Answer 1

En las declaraciones de su caso necesita cambiar [ALL] a ''ALL'' , recordando usar dos comillas simples mientras construye un valor de cadena:

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

Answer 2

Como entiendo, ALL es la suma de todos los valores en esta fila. Por lo tanto, debe volver a escribir su código SP (según los datos que proporcionó):

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