| Author |
 Topic  |
|
|
tinytoontown
Starting Member
United Kingdom
3 Posts |
 Posted - 04/20/2012 : 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
India
47189 Posts |
Posted - 04/20/2012 : 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
United Kingdom
3 Posts |
Posted - 04/27/2012 : 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 |
Edited by - tinytoontown on 04/27/2012 10:33:32 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 04/27/2012 : 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
United Kingdom
3 Posts |
Posted - 04/28/2012 : 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
India
47189 Posts |
Posted - 04/28/2012 : 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/
|
|
|
| |
Topic |
|
|
|
Coalesce
