Bhuvnesh (12/31/2012)
Yes, i have also heard this so many times that RBAR gives the result in much optimal way btu BAD thing is i never got any chance to use it (actually i always keep my self away from it, as i found it a bit complex to frame and implement it 😛 ) but here in your example, the output is not matching with solution provided above by "Sean Lange".if you help us to frame the query acc to "Sean Lange" result then it will be another good/alternative solution.
That´s so easy to get with the code I gave that I'm not sure why you're asking for it.
Using the ddl and sample data provided by Sean, here's the solution.
DECLARE @SQL2nvarchar(300)
SELECT @SQL2 = 'SELECT ' + STUFF( CASE WHEN COUNT(ID) > 0 THEN ', ID' ELSE '' END
+ CASE WHEN COUNT(C1) > 0 THEN ', C1' ELSE '' END
+ CASE WHEN COUNT(C2) > 0 THEN ', C2' ELSE '' END
+ CASE WHEN COUNT(C3) > 0 THEN ', C3' ELSE '' END
+ CASE WHEN COUNT(C4) > 0 THEN ', C4' ELSE '' END
+ CASE WHEN COUNT(C5) > 0 THEN ', C5' ELSE '' END
+ CASE WHEN COUNT(C6) > 0 THEN ', C6' ELSE '' END, 1,1, '') + ' FROM Temp'
FROM Temp
-- PRINT @SQL2
EXEC SP_EXECUTESQL @SQL2