Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to find column is having value or not Expand / Collapse
Author
Message
Posted Thursday, December 27, 2012 9:36 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 336, Visits: 1,148
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!
Post #1400785
Posted Thursday, December 27, 2012 11:20 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #1400797
Posted Friday, December 28, 2012 12:05 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 336, Visits: 1,148
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!

Post #1400807
Posted Friday, December 28, 2012 12:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #1400811
Posted Friday, December 28, 2012 9:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
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 Moden's 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)
Post #1400953
Posted Friday, December 28, 2012 11:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1400993
Posted Friday, December 28, 2012 11:51 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 3,559, Visits: 7,681
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1400997
Posted Monday, December 31, 2012 12:42 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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 ) 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
Post #1401301
Posted Monday, December 31, 2012 7:22 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 3,559, Visits: 7,681
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 @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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1401385
Posted Monday, December 31, 2012 7:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:50 AM
Points: 1,949, Visits: 8,315
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
Kent user group
Post #1401393
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse