I am using SQL Server 2014. I am working a stored procedure that returns a table.
We have two input parameters, @from and @until, which define start and end of date ranges we will be searching by.
At the end of our query, we use PIVOT to turn columns into rows.
I am able to SELECT the data correctly, but have one problem after the PIVOT: many of the columns come back fully NULL, because they were not included in the date range set by @from and @until.
What can I do so that I don't get those NULL columns at all?
Here is my code:
ALTER PROCEDURE [dbo].[presupuestosVenta]
@from char(8), (in this case 201501)
@until char(8) (in this case 201506)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @venta TABLE
(
LAPSO_DOC CHAR(6),
ID_EXT_ITM CHAR(3),
ID_TALLA CHAR(6),
VENTA INT
)
INSERT INTO @venta
SELECT
LAPSO_DOC,
ID_EXT_ITM,
ID_TALLA,
CAST (CANTIDAD_CAP AS INT) AS VENTA
FROM dbo.a_presupuestosVenta
WHERE
( ID_REFER = '312177000'
OR ID_REFER = '314001000'
OR ID_REFER = '315099000'
)
AND LAPSO_DOC BETWEEN @from AND @until
SELECT *
FROM @venta
PIVOT
(
SUM(VENTA)
FOR LAPSO_DOC
IN ([201501],[201502], [201503], [201504],[201505], [201506] )
)
AS PivotTable
ORDER BY ID_EXT_ITM DESC, ID_TALLA ASC
END
Here are the results I am getting:
EDIT: I've been working on a DYNAMIC PIVOT QUERY, but keep getting an error saying my table @venta is not defined:
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', v.' + QUOTENAME(LAPSO_DOC)
FROM (SELECT v.LAPSO_DOC FROM @venta AS v
GROUP BY v.LAPSO_DOC) AS x;
SET @sql = N'
SELECT ' + STUFF(@columns, 1, 2, '') + '
FROM
(
SELECT v.VENTA, v.LAPSO_DOC
FROM @venta as v
) AS j
PIVOT
(
SUM(VENTA) FOR LAPSO_DOC IN ('
+ STUFF(REPLACE(@columns, ', v.[', ',['), 1, 1, '')
+ ')
) AS v;';
PRINT @sql;
EXEC sp_executesql @sql;
