CODEWITHSUNDEEP
sql-server
Black_Dragon_85
Black_Dragon_85

Reputation: 1

How can I use ISNULL in dynamic pivot sql?

I have a table like below, when I'm using dynamic pivot everything is ok till I want to sum all rows. I have a problem to change NULL's into 0. In which part of query should put the ISNULL function?

table:

klucz      logid_do_zliczenia   duration
556267831171038 1171038 1058
556267831171038 1171038 54
556267831170688 1170688 72
556267831171038 1171038 59
556267831170688 1170688 46
556267831171038 1171038 54
556267831170688 1170688 42
556267831171038 1171038 12
556267831170634 1170634 112
556267831170634 1170634 53
556267831141392 1141392 77
556290551161102 1161102 32
556290551140814 1140814 249
556290551140814 1140814 43
556290551140814 1140814 105
556290551140814 1140814 40
556290551140814 1140814 304
556290551140814 1140814 61
556290551150534 1150534 351
556290551150534 1150534 76
556290551141170 1141170 238

Query:

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);             

SET @Columns = (SELECT  N', p.' + QUOTENAME(REPLACE(CONVERT(VARCHAR(max), p.logid_do_zliczenia, 111), '/', '-'))
                FROM    #3 AS p
                GROUP BY p.logid_do_zliczenia
                ORDER BY p.logid_do_zliczenia
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)');            



SET @sql = N'SELECT klucz, ' + STUFF(@columns, 1, 2, '') + ',

Suma = ' + STUFF(REPLACE(@columns, ', p.[', ' + p.['), 1, 3, '') + '
FROM
(
  SELECT p.logid_do_zliczenia, p.klucz, p.duration FROM #3 AS p
) AS j
PIVOT
(
  SUM(duration) FOR logid_do_zliczenia IN ('+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '') + ')
) AS p;';
EXEC sp_executesql @sql;

How can I change it?

Upvotes: 0

Views: 875

Answers (1)

SQLChao
SQLChao

Reputation: 7837

Try this

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);             

SET @Columns = (SELECT  N', p.' + QUOTENAME(REPLACE(CONVERT(VARCHAR(max), p.logid_do_zliczenia, 111), '/', '-'))
                FROM    #3 AS p
                GROUP BY p.logid_do_zliczenia
                ORDER BY p.logid_do_zliczenia
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)');            



SET @sql = N'SELECT klucz, ' + STUFF(@columns, 1, 2, '') + ',

Suma = ' + STUFF(REPLACE(@columns, ', p.[', ',0) + ISNULL(p.['), 1, 3, '') + ',0)
FROM
(
  SELECT p.logid_do_zliczenia, p.klucz, p.duration FROM #3 AS p
) AS j
PIVOT
(
  SUM(duration) FOR logid_do_zliczenia IN ('+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '') + ')
) AS p;';

EXEC sp_executesql @sql;

Upvotes: 1

Related Questions