Coalesce - 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

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Coalesce

Author

Topic

tinytoontown
Starting Member

3 Posts

Posted - 2012-04-20 : 04:38:58

Morning all
i have a qry using coalesce(nullif(...)) spanning 12 columns which i want to use as a parameter drop down in SSRS, my first 2 parameter coding work fine, now, where i am struggling is dynamically getting reults for my 3rd script as the data i need could be in any one of the columns.

i have searched (but not found), so need to know
1) is there a way i can discount "unused" columns ?
2) which of the 12 columns did the coalesce find data in?
3) and as the columns are in order, can i do select distinct on the next column accross

SELECT DISTINCT coalesce(nullif(area_cde12,''),nullif(area_cde11,''),nullif(area_cde10,''),nullif(area_cde9,''),nullif(area_cde8,''),nullif(area_cde7,''),nullif(area_cde6,''),nullif(area_cde5,''),nullif(area_cde4,''),nullif(area_cde3,''),nullif(area_cde2,''),nullif(area_cde1,'')) AS item_value

FROM #temp

tiny

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-20 : 15:28:08

do you mean selectively showing columns in SSRS based on whether they've value of not?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tinytoontown
Starting Member

3 Posts

Posted - 2012-04-27 : 10:32:02

sorry for the delay

I am looking at the SQL side of my problem for the moment.

what i need to figure out is of the 12 columns i am screening, which one of them had the first result in.

so in this example i could get a result to = col5

COL1 COL2 COL3 COL4 COL5 COL6..... and so on
NULL NULL NULL NULL DATA DATA.....

thanks again

tiny

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-27 : 16:11:39

for that you can just do

COALESCE(COL1,COL2,COL3,COL4,COL5)

and it will return you first not null value out of them

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tinytoontown
Starting Member

3 Posts

Posted - 2012-04-28 : 02:18:31

Thanks, but that bit I am aware of.

What I need to know is the name of the column the first result was found in.

Thanks

tiny

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-28 : 19:18:35

you can unpivot and then check for not null values and get the corresponding column name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources