how to exclude null value for pivot query if any of the row value is null

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

date	18 	20	50	909 --headers
2015-03-30 	20	153	92	41
2015-03-31 	20	153	93	41
2015-02-19 	571	160	92  NULL
2015-04-06 	52	155	95	42
2015-04-07 	80	155	95	42

out put should be like this

date	18 	20	50	909 --headers
2015-03-30 	20	153	92	41
2015-03-31 	20	153	93	41
2015-02-19 	571	160	92  NULL
2015-04-06 	52	155	95	42
2015-04-07 	80	155	95	42

Posted 24-Aug-15 22:37pm

Member 11844168

Updated 24-Aug-15 23:27pm

Maciej Los

v3

Add a Solution

Comments

Wendelius 25-Aug-15 4:47am

What is the query you have?

Member 11844168 25-Aug-15 4:58am

declare @fromDate varchar(20)
declare @ToDate varchar(20)
declare @ymd varchar(2)
declare @num smallint
set @ymd='m'
set @num=10

if @ymd<>''
BEGIN
set @ToDate= convert (varchar(20),(select MAX(navdate) from mutual.dbo.latnav),106)
if @ymd ='y'
set @fromdate= convert (varchar(20),DATEADD(year,-@num,@ToDate),106)
else if @ymd='m'
set @fromdate= convert (varchar(20),DATEADD(MONTH,-@num,@ToDate),106)
else if @ymd='d'
set @fromDate= convert (varchar(20),DATEADD(day,-@num,@ToDate),106)
END

begin

exec(
'select *
from
(
select s.code,p.date,p.rs from Price as p
inner join SCH as s
on s.CODE=p.CODE
where s.CODE in (18,20,50,909)
and p.date BETWEEN ' +' '''+@fromdate+''' '+ ' and '+' '''+@todate+''' '+ '

) dataTable
pivot
(
sum(rs)
for code in ( [18],[20],[50],[909] )
) as pivotTable
order by 2'

)

end

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

manognya kota · Answer 1 · 2015-08-24T23:15:00

Solution 1

Hi,

As i understand you are doing a select * in the pivot table.
so,

Can you select like .. select * [your pivot table] t where [t.requiredcolumn] is not null.

something like,

SQL

select * from
 ( dataTable1
pivot 
datatable2)

 as pivotTable where reqcol is not null

order by 2

Hope this helps.

Happy coding!

Posted 24-Aug-15 23:15pm

manognya kota

Updated 25-Aug-15 0:14am

v3

Comments

Andy Lanng 25-Aug-15 5:17am

psst. Use square brackets or they'll be inserted as markup :P

manognya kota 25-Aug-15 5:54am

i have used <> to indicate generic way. Thanks for updating :)

Member 11844168 25-Aug-15 5:18am

this will not work i have tried it

Andy Lanng 25-Aug-15 5:29am

have you tried it this way:
pivot
(
sum(rs)
for code in ( [18],[20],[50],[909] )
) as pivotTable
where [909] is not null
order by 2'

Member 11844168 25-Aug-15 6:03am

thank you :-)

Maciej Los · Answer 2 · 2015-08-24T23:30:00

A source query should looks like this:

SQL

select s.code,p.date,p.rs from Price as p
inner join SCH as s
on s.CODE=p.CODE
where s.CODE in (18,20,50,909) AND NOT p.rs IS NULL
and p.date BETWEEN ' +' '''+@fromdate+''' '+ ' and '+' '''+@todate+''' '+ '

[EDIT]
If sourcce table does not contain NULL values, you have to filter pivot table:

SQL

SELECT pt.<Fields>
FROM (
    -- source query
) AS st
PIVOT (...) AS pt
WHERE NOT pt.[909] IS NULL

Another way is to replace NULLs with default values via using COALESCE function[^]:

SQL

SELECT COALESCE(pt.[18], 0) AS [18] -- and so on