How to find column is having value or not

Question

How to find column is having value or not

yuvipoy

SSChampion

Points: 10469
More actions
December 27, 2012 at 9:36 pm

#272067

Hi,
How to find column is having value or not
Id Col_1 Col_2 Col_3 Col_4 Col_5 Col_6
1 1 NULL 2 NULL 3 NULL
1 9 NULL 8 NULL 4 NULL
Expected ans
Id, Col_1,Col_3,Col_5
which is having some value other than NULL
Thanks!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

yuvipoy (12/27/2012)
Hi,
How to find column is having value or not
Id Col_1 Col_2 Col_3 Col_4 Col_5 Col_6
1 1 NULL 2 NULL 3 NULL
1 9 NULL 8 NULL 4 NULL
Expected ans
Id, Col_1,Col_3,Col_5
which is having some value other than NULL
Thanks!

i dont know whats your actual requirement but definitely not what have you explained in your above query.

anyways, you can restrict/make conditional the records or data display but NOT the columns . select part will be always confirmed and unconditional.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

yuvipoy SSChampion Points: 10469 More actions · Answer 2

I got the Solution

Go

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,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

Assuming that there will not be any change in the data for the ID

say for id=1 the values will be present only C1,C3,C5 rest of the columns will be null for whole id=1

where as for id=2 the values may present in C4,C8,C9 rest of the columns will be null for whole id=2

Thanks for your Time!

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

Glad you got the solution yourself but i would suggest you to use set based or while loop instead of cursor

see Comparing cursor vs. WHILE loop performance in SQL Server 2008

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

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

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 5

Bhuvnesh (12/28/2012)
Glad you got the solution yourself but i would suggest you to use set based or while loop instead of cursor
see Comparing cursor vs. WHILE loop performance in SQL Server 2008

I've found that comparisons between Cursor and While loops is like comparing the "pouring" speed between Molasses and High Fructose Corn Syrup. Depenind on your preferences, one looks better than the other but neither is designed for "pouring". 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

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

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 7

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

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.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

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

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 9

Just out of interest, what business problem are you fancing in the real world ?

This sort of problem always strikes be as

a) an academic exercise

b) a poor database design

If b then maybe we can address what the real problem is ?

Clear Sky SQL
My Blog[/url]