Luis Cazares (12/28/2012)

---

As others have said, neither CURSORS or WHILE LOOPS are effective. Here's a better solution that might help with this problem. I'm just publishing this to avoid that others would stay with the RBAR solution.

I'm including two possible solutions depending on what is needed. Can you understand it, yuvipoy?

USE Test

GO

IF EXISTS( SELECT 1 FROM sys.tables WHERE name = 'Test')

DROP TABLE Test

CREATE TABLE Test(

IDintIDENTITY,

Col1int NULL,

Col2int NULL,

Col3int NULL,

Col4int NULL,

Col5int NULL,

Col6int NULL)

INSERT INTO Test VALUES

( 1, NULL, 3, NULL, 4, NULL),

( 2, 7, NULL, 6, NULL, NULL)

SELECT STUFF( CASE WHEN ID IS NOT NULL THEN ', ID' ELSE '' END

+ CASE WHEN Col1 IS NOT NULL THEN ', Col1' ELSE '' END

+ CASE WHEN Col2 IS NOT NULL THEN ', Col2' ELSE '' END

+ CASE WHEN Col3 IS NOT NULL THEN ', Col3' ELSE '' END

+ CASE WHEN Col4 IS NOT NULL THEN ', Col4' ELSE '' END

+ CASE WHEN Col5 IS NOT NULL THEN ', Col5' ELSE '' END

+ CASE WHEN Col6 IS NOT NULL THEN ', Col6' ELSE '' END, 1,1, '') AS Not_Nulls,

*

FROM Test

SELECT STUFF( CASE WHEN COUNT( ID) = COUNT(*) THEN ', ID' ELSE '' END

+ CASE WHEN COUNT( Col1) = COUNT(*) THEN ', Col1' ELSE '' END

+ CASE WHEN COUNT( Col2) = COUNT(*) THEN ', Col2' ELSE '' END

+ CASE WHEN COUNT( Col3) = COUNT(*) THEN ', Col3' ELSE '' END

+ CASE WHEN COUNT( Col4) = COUNT(*) THEN ', Col4' ELSE '' END

+ CASE WHEN COUNT( Col5) = COUNT(*) THEN ', Col5' ELSE '' END

+ CASE WHEN COUNT( Col6) = COUNT(*) THEN ', Col6' ELSE '' END, 1,1, '') AS Not_Nulls,

STUFF( CASE WHEN COUNT( ID) = 0 THEN ', ID' ELSE '' END

+ CASE WHEN COUNT( Col1) = 0 THEN ', Col1' ELSE '' END

+ CASE WHEN COUNT( Col2) = 0 THEN ', Col2' ELSE '' END

+ CASE WHEN COUNT( Col3) = 0 THEN ', Col3' ELSE '' END

+ CASE WHEN COUNT( Col4) = 0 THEN ', Col4' ELSE '' END

+ CASE WHEN COUNT( Col5) = 0 THEN ', Col5' ELSE '' END

+ CASE WHEN COUNT( Col6) = 0 THEN ', Col6' ELSE '' END, 1,1, '') AS Only_Nulls

FROM Test