[updated the fields in else part of case..if forgot to replace in else part]try this..
I am not good in sql but I tried this and according to your requirement its perfectly working..
select case when isnull(ltrim(rtrim(houseno)),'') + ',' + isnull(ltrim(rtrim(city)),'') + ',' + isnull(ltrim(rtrim(state)),'') =',,' then ' ' else isnull(ltrim(rtrim(houseno)),'') + ',' + isnull(ltrim(rtrim(city)),'') + ',' + isnull(ltrim(rtrim(state)),'') end as address from [tablename]
make it answer if you get your solution.
Thanks