SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to find column is having value or not


How to find column is having value or not

Author
Message
yuvipoy
yuvipoy
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2278 Visits: 1387
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!
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12756 Visits: 4077
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
yuvipoy
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2278 Visits: 1387
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
Bhuvnesh
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12756 Visits: 4077
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
Sean Lange
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60124 Visits: 17951
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207050 Visits: 41961
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40455 Visits: 19808
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(
ID int IDENTITY,
Col1 int NULL,
Col2 int NULL,
Col3 int NULL,
Col4 int NULL,
Col5 int NULL,
Col6 int 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
Bhuvnesh
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12756 Visits: 4077
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(
ID int IDENTITY,
Col1 int NULL,
Col2 int NULL,
Col3 int NULL,
Col4 int NULL,
Col5 int NULL,
Col6 int 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 :-P ) 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
Luis Cazares
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40455 Visits: 19808
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 :-P ) 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 @SQL2 nvarchar(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
Dave Ballantyne
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6836 Visits: 8370
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search