SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL pivot table and ISNULL?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rasta Pickles
Posting Yak Master

United Kingdom
169 Posts

Posted - 05/18/2013 :  12:02:29  Show Profile  Reply with Quote
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

United Kingdom
169 Posts

Posted - 05/18/2013 :  12:04:13  Show Profile  Reply with Quote
Just to clarify, I'm looking to display a zero instead of "NULL".
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/18/2013 :  12:37:38  Show Profile  Reply with Quote
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
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
169 Posts

Posted - 05/18/2013 :  12:43:04  Show Profile  Reply with Quote
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).
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 05/19/2013 :  08:05:55  Show Profile  Reply with Quote
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
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
169 Posts

Posted - 05/19/2013 :  10:58:40  Show Profile  Reply with Quote
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'.
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
169 Posts

Posted - 05/19/2013 :  11:14:54  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 05/20/2013 :  08:24:18  Show Profile  Reply with Quote
You are very welcome - glad to help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000