Thanks much Luis for the help I used instead of IIF the alternative-

select *, cast (

case

when [ES Fully approved data 1] = ''

and[ES Fully approved data 2] = ''

and [ES Fully approved data 3] = ''

and[ES Fully approved data 4] = ''

and [ES Fully approved data 5] = ''

and [ES Fully approved data 6] = ''

and [ES Fully approved data 7] = ''

and [ES Fully approved data 8] = ''

then

null

else

case

when [ES Fully approved data 1] = ''

then ''

else [ES Fully approved data 1] + ';'

end

+

case

when [ES Fully approved data 2] = ''

then ''

else [ES Fully approved data 2] + ';'

end

+

case

when [ES Fully approved data 3] = ''

then ''

else [ES Fully approved data 3] + ';'

end

+

case

when [ES Fully approved data 4] = ''

then ''

else [ES Fully approved data 4] + ';'

end

+

case

when [ES Fully approved data 5] = ''

then ''

else [ES Fully approved data 5] + ';'

end

+

case

when [ES Fully approved data 6] = ''

then ''

else [ES Fully approved data 6] + ';'

end

+

case

when [ES Fully approved data 7] = ''

then ''

else [ES Fully approved data 7] + ';'

end

+

case

when [ES Fully approved data 8] = ''

then ''

else [ES Fully approved data 8]

end

endas varchar(255))as [ES Fully approved final data to display]

from #tmp_SLII_FINAL

It gives me the desired O/P now.

RegardsDJ