CODEWITHSUNDEEP
sql-servert-sqlpivot
Joao Povoa
Joao Povoa

Reputation: 23

I want 0s instead of null values in query using pivot table and dynamic column names

This query returns the sales from each client distributed by week and year. This way I can have results from different years without mixing the sales from different years in the same week.

But I get NULL values in the weeks where there is no data to show and want to get 0s instead. The query is as follows:

    DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
    DECLARE @ColumnName AS NVARCHAR(MAX)

    --Get distinct values of the PIVOT Column 
    SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
    + QUOTENAME(courses.anomes)
    FROM 
    (
    SELECT DISTINCT right('0'+ltrim(rtrim(str(datepart(week,ft.fdata))))+'/'+ltrim(rtrim(str(ft.ftano))),7) anomes FROM ft where ftano=2015 
    ) Courses
    order by anomes

    --Prepare the PIVOT query using the dynamic 
    SET @DynamicPivotQuery = 
      N'SELECT nome, ' + @ColumnName + '
        FROM 
        (
        select nome,
right('+'''0'''+'+ltrim(rtrim(str(datepart(week,ft.fdata))))+'+'''/'''+'+ltrim(rtrim(str(ft.ftano))),7) anomes,etotal from ft where ftano=2015
        ) p
PIVOT(SUM(Etotal) 
      FOR anomes IN (' + @ColumnName + ')) AS PVTTable'
      --Execute the Dynamic Pivot Query
      EXEC sp_executesql @DynamicPivotQuery

Upvotes: 2

Views: 54

Answers (1)

TT.
TT.

Reputation: 16137

In analogy with @ColumnName, make a @ColumnNameSelect that selects ISNULL(pivot_col,0) and use that in your SELECT clause.

Update Due to some missing basic information (like the ft table) I was not able to test what I answered. I rewrote the @ColumnNameSelect using STUFF which is a better way. You should apply this way of working to building the @ColumnName list as well. To test this I created a temporary table #ft to use in place of your ft table. To run this replace #ft AS ft with ft in the script.

CREATE TABLE #ft(fdata DATETIME,ftano INT,nome VARCHAR(256),etotal INT); 
INSERT INTO #ft(fdata,ftano,nome,etotal)VALUES
    ('20151220',2015,'nome1',16),
    ('20151201',2015,'nome2',9),
    ('20151116',2015,'nome3',25),
    ('20151010',2015,'nome4',11);

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX);
DECLARE @ColumnName AS NVARCHAR(MAX);

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
+ QUOTENAME(courses.anomes)
FROM 
(
SELECT DISTINCT right('0'+ltrim(rtrim(str(datepart(week,ft.fdata))))+'/'+ltrim(rtrim(str(ft.ftano))),7) anomes FROM #ft AS ft where ftano=2015 
) Courses
order by anomes

DECLARE @ColumnNameSelect AS NVARCHAR(MAX)
SELECT @ColumnNameSelect=STUFF((
    SELECT
    ',ISNULL('+QUOTENAME(courses.anomes)+',0) AS '+QUOTENAME(courses.anomes)
    FROM 
    (
    SELECT DISTINCT right('0'+ltrim(rtrim(str(datepart(week,ft.fdata))))+'/'+ltrim(rtrim(str(ft.ftano))),7) anomes FROM #ft AS ft where ftano=2015 
    ) Courses
    order by anomes
    FOR XML PATH('')
    ),1,1,'');
PRINT @ColumnNameSelect

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
    N'SELECT nome, ' + @ColumnNameSelect + '
    FROM 
    (
    select nome,
right('+'''0'''+'+ltrim(rtrim(str(datepart(week,ft.fdata))))+'+'''/'''+'+ltrim(rtrim(str(ft.ftano))),7) anomes,etotal from #ft AS ft where ftano=2015
    ) p
PIVOT(SUM(Etotal) 
    FOR anomes IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery;

DROP TABLE #ft;

This prints out:

+-------+---------+---------+---------+---------+
| nome  | 41/2015 | 47/2015 | 49/2015 | 52/2015 |
+-------+---------+---------+---------+---------+
| nome1 |       0 |       0 |       0 |      16 |
| nome2 |       0 |       0 |       9 |       0 |
| nome3 |       0 |      25 |       0 |       0 |
| nome4 |      11 |       0 |       0 |       0 |
+-------+---------+---------+---------+---------+

Upvotes: 2

Related Questions