SQL pivot table and ISNULL? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL pivot table and ISNULL?

Author

Topic

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-05-18 : 12:02:29

Can't get rid of the NULLS no matter where I wrap an ISNULL statement, any help appreciated before I pull all of my hair out.

Here's some ready-to-use dummy code:


USE tempdb;
GO

CREATE TABLE dbo.Products
(
  ProductID VARCHAR (50),
  Period    VARCHAR (6),
  Value     DECIMAL (15, 2)
);
INSERT dbo.Products VALUES
('North', '201201', 100),
('South', '201202', 500);

SELECT ProductID, Period, Value
FROM dbo.Products AS p;
  
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(Period)
  FROM (SELECT p.Period FROM dbo.Products AS p
  GROUP BY p.Period) AS x;
SET @sql = N'
SELECT * 
FROM
(
  SELECT ProductID, Period, Value
   FROM dbo.Products AS p
) AS j
PIVOT
(
  SUM(Value) FOR Period IN ('
  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
  + ')
) AS p;';

PRINT @sql;
EXEC sp_executesql @sql;

DROP TABLE dbo.Products

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-05-18 : 12:04:13

Just to clarify, I'm looking to display a zero instead of "NULL".

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-18 : 12:37:38

quote:
Originally posted by Rasta Pickles

Just to clarify, I'm looking to display a zero instead of "NULL".

Is this what you are looking for:
http://www.sqlservercentral.com/Forums/Topic1173247-392-1.aspx

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-05-18 : 12:43:04

No; in the example you've linked to, the columns are hardcoded.

I am using dynamic SQL so I assume a different set of rules apply (or maybe I'm asking for something that just isn't do-able).

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-19 : 08:05:55

Create a dynamic list for the select list and use that list instead of the "select *", like shown below in red

USE tempdb;
GO

CREATE TABLE dbo.Products
(
  ProductID VARCHAR (50),
  Period    VARCHAR (6),
  Value     DECIMAL (15, 2)
);
INSERT dbo.Products VALUES
('North', '201201', 100),
('South', '201202', 500);

SELECT ProductID, Period, Value
FROM dbo.Products AS p;
  
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(Period)
  FROM (SELECT p.Period FROM dbo.Products AS p
  GROUP BY p.Period) AS x;

declare @selectList nvarchar(max);
set @selectList = N'';
select @selectList += N',ISNULL(p.' + QUOTENAME(Period) + ',0)'
  FROM (SELECT p.Period FROM dbo.Products AS p
  GROUP BY p.Period) AS x;
set @selectList = stuff(@selectlist,1,1,'');

SET @sql = N'
SELECT ProductId, ' + @selectList + '
FROM
(
  SELECT ProductID, Period, Value
   FROM dbo.Products AS p
) AS j
PIVOT
(
  SUM(Value) FOR Period IN ('
  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
  + ')
) AS p;';

PRINT @sql;
EXEC sp_executesql @sql;

DROP TABLE dbo.Products

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-05-19 : 10:58:40

Thank you for your reply, I appreciate you having a stab at my problem.

Alas:


Msg 213, Level 16, State 1, Line 8
Column name or number of supplied values does not match table definition.
Msg 207, Level 16, State 1, Line 12
Invalid column name 'Period'.
Msg 207, Level 16, State 1, Line 12
Invalid column name 'Value'.
Msg 207, Level 16, State 1, Line 19
Invalid column name 'Period'.
Msg 207, Level 16, State 1, Line 18
Invalid column name 'Period'.
Msg 207, Level 16, State 1, Line 25
Invalid column name 'Period'.
Msg 207, Level 16, State 1, Line 24
Invalid column name 'Period'.

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-05-19 : 11:14:54

Ignore me, ignore me - your solution worked an absolute treat, thank you so much!!!!

(I think I had a table already called dbo.products from some other SQL testing and it was getting confused.....renaming the tables in your code to dbo.products3 did the trick).

Outstanding sir, thank you again

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-20 : 08:24:18

You are very welcome - glad to help.

king_fisher
Starting Member

13 Posts

Posted - 2014-06-17 : 09:06:01

its working ,but Its shows column Name as 'No column Name' ,'No Cloumn Name'

vijay nelson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources