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