display only columns with not nul values

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

display only columns with not nul values

Author

Topic

fateh
Starting Member

1 Post

Posted - 2013-07-04 : 16:07:02

Hi

I want to build a report and after processing I have table like that

Style QTY size1 size2 size3 size4 ....
AAA 5 2 null 3 null
BBB 4 null 1 2 1

I want in my report diplay only columns with the value for each style like :

Style QTY size1 size3 ....

AAA 5 2 3

size2 size3 size4 ...
BBB 4 1 2 1

Can you help me please ?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-04 : 17:10:35

You can copy and paste this example to see what it does and adapt it to your needs

create table #tmp (id int, s1 int, s2 int, s3 int, s4 int);
insert into #tmp values (1,null,null,null,1),(2,8,null,9,null),(3,null,11,null,22);

;with cte as 
(
	select *, 
		row_number() over(partition by id order by cols)  as N 
	from #tmp 
	unpivot (val for cols in ([s1],[s2],[s3],[s4]))U
)
select 
	id,[1] size1,[2] size2,[3] size3,[4] size4
from 
	(select id,val,n from cte ) s 
pivot 
	(max(val) for N in ([1],[2],[3],[4]))p 
order by 
	id;

drop table #tmp;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-05 : 02:07:50

Reading your requirement, it seems like a presentation issue to me

If you dont want to show the columns which are having null values, you can set the visibility property an expression in report to hide or show them. But keep in mind that it will get hidden only when all values inside it are null. if at all there's a single non null value for a Style it will display it.

The exact format you're showing can be obtained only by placing a group on Style field in the report and then use count logic for fields inside the group to check if they've atleast one non null value.
so hidden expression will look like

=IIF(SUM(IIF(Fields!size1.value Is Nothing,0,1),"YourGroupName")>0 ,False,True)
similarly for all of the other size fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources