Cheers mate. I am sure that all this brain sharing definitely leads to 
something..... ;-)\

Thanks for posting your solution,



Imar


At 10:54 AM 9/29/2002 -0700, you wrote:

>Hi Imar,
>
>Well, that is why we are all here. To share our brains. :-)
>
>Here you go...
>
>I was running a select script that gave the following resultset. (helped 
>by Les)
>
>COL1     COL2           COL3
>-------------------------------------------------
>ABC       somedata1   500
>ABC       somedata2   500
>ABC       NULL           1000
>EFG       Junk1           300
>EFG       Junk2           200
>EFG       NULL            500
>NULL      NULL           1500
>-------------------------------------------------------
>I disliked the display of NULL inbetween, so I wanted to make them show 
>some meaningful data, since it was to be used by other teams here. So I 
>was in search of it and posted. By then I too tried on my own here and was 
>through. ;-)
>
>SELECT COL1, "ALIAS_COL2" 
>  CASE
>   WHEN COL1 IS NULL and COL2 IS NULL THEN "Grand Total"
>   WHEN COL1 = "ABC" and COL2 IS NULL THEN Source + "ABC Sub Total"
>   WHEN COL1 = "EFG" and COL2 IS NULL THEN Source + "EFG Sub Total"
>   ELSE cast(COL2 as varchar(5))  --- since it is a int type, I had to use 
> cast()
>  End,
>  SUM(COL3) AS Total FROM
>Select .....................................................
>
>But there was no example in BOL for using multiple columns in single CASE, 
>AFAIK. One thing I found was, before using the other columns in CASE .. 
>WHEN... THEN, one should have defined it in the SELECT list.
>
>In the above eg: COL1 is defined in SELECT list, before its usage in 
>CASE...WHEN...THEN, where I check its value along with COL2.
>
>Hope it benefits someone there.
>
>Cheers!!!
>Vijay G