Not only is Bhuvnesh absolutely correct about not using a loop for this but what are you going to do when there are nulls in some rows and not in others?

Consider the following to see what I mean. I only changed 1 insert from C5 to C4.

CREATE TABLE TEMP (

id INT

,C1 INT

,C2 INT

,C3 INT

,C4 INT

,C5 INT

,C6 INT

)

GO

INSERT INTO TEMP (

id

,C1

,C3

,C5

)

VALUES (

1

,2

,3

,5

)

INSERT INTO TEMP (

id

,C1

,C3

,C4 --changed from C5

)

VALUES (

1

,6

,7

,8

)

SELECT *

FROM TEMP

GO

DECLARE @name VARCHAR(100)

DECLARE @SQL1 NVARCHAR(max)

DECLARE @sqlCommand NVARCHAR(max)

DECLARE @OUPUT VARCHAR(max)

DECLARE @flg INT

SET @flg = 0

SET @OUPUT = ''

DECLARE db_cursor CURSOR

FOR

SELECT NAME

FROM sys.syscolumns

WHERE id IN (

SELECT id

FROM sys.sysobjects

WHERE NAME = 'TEMP '

)

OPEN db_cursor

FETCH NEXT

FROM db_cursor

INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sqlCommand = 'SELECT @flg=1 from TEMP where LEN( ' + @name + ')' + '> 0 '

print @sqlCommand

print @flg

EXECUTE sp_executesql @sqlCommand

,N'@name nvarchar(100),@flg int =0 OUTPUT'

,@name = @name

,@flg = @flg OUTPUT

IF @flg = 1

SET @OUPUT = @OUPUT + @name + ', '

SET @flg = 0

FETCH NEXT

FROM db_cursor

INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

SET @SQL1 = 'SELECT ' + LEFT(@OUPUT, LEN(@OUPUT) - 1) + ' FROM TEMP '

EXEC SP_EXECUTESQL @SQL1

GO

DROP TABLE TEMP