I'm using a SQL Pivot to list the total value of sales per day per product and I'm having trouble replacing the NULL values of my rows.
The following table is an example of my data
Date | Product | Amount | Price |
------------+---------+----------+--------+
2018-01-06 | PRO 1 | 2 | 5 |
2018-01-06 | PRO 2 | 3 | 6 |
2018-01-06 | PRO 2 | 1 | 6 |
2018-01-06 | PRO 3 | 4 | 18 |
2018-01-06 | PRO 4 | 2 | 5 |
2018-01-06 | PRO 5 | 3 | 6 |
2018-01-06 | PRO 6 | 2 | 3 |
2018-01-06 | PRO 6 | 7 | 3 |
2018-01-07 | PRO 6 | 7 | 3 |
My query:
DECLARE @cols AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';
SELECT @cols = @cols + QUOTENAME([Product]) + ',' FROM (select distinct [Product] from #sales) as tmp
SELECT @cols = substring(@cols, 0, len(@cols)) --trim "," at end
set @query =
'SELECT * from (
Select [Date], ([Amount]*[Price]) as [TOTAL], [Product] from #Sales group by Date, Product, Amount, Price
) src
pivot
(
SUM([TOTAL]) for [Product] in (' + @cols + ')
) piv'
execute(@query)
My result:
Date | PRO 1 | PRO 2 | PRO 3 | PRO 4 | PRO 5 | PRO 6 |
-----------+--------+--------+--------+--------+--------+--------|
2018-01-06 | 10 | 24 | 72 | 10 | 18 | 27 |
2018-01-07 | NULL | NULL | NULL | NULL | NULL | 21 |
How can I replace the NULL values with 0? Doing ISNULL(([Amount]*[Price]),0) does not seem to do anything for it.
You must replace * with a dynamic list of columns, and use ISNULL there. The code to generate this list could be something like that:
DECLARE @cols2 AS NVARCHAR(MAX)='[Date], ';
SELECT @cols2 = @cols2 + 'ISNULL(' + QUOTENAME([Product]) + ', 0) as ' + QUOTENAME([Product]) + ',' FROM (select distinct [Product] from #sales) as tmp
SELECT @cols2 = substring(@cols2, 0, len(@cols2)) --trim "," at end
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments