March 3, 2012 at 1:21 pm
Please Help me.
I have say a table with a number of columns
eg. SampleTable (PKC1,C2,C3,C4,C5, C6)
and values like
PKC1 C2 C3 C4 C5 C6
1 a NULL b NULL f
2 d NULL e NULL NULL
3 g NULL h NULL h
Now I want to retrieve values with columns that has at least one Non NULL Values (In other words identifying columns having at least one none NULL value and retrieve based on those columns).Is it possible? and could you help me on this regard?
March 3, 2012 at 3:33 pm
Wats your expected result from that sample table?
March 4, 2012 at 4:57 am
My Expected result will be ,remove Column C3 and C5 and returns the other as it is
The orginal value is
PKC1 C2 C3 C4 C5 C6
1 a NULL b NULL f
2 d NULL e NULL NULL
3 g NULL h NULL h
The expected result is
PKC1 C2 C4 C6
1 a b f
2 d e NULL
3 g h h
Just detect and remove all columns with no values at all ( columns with NULL values in all the records)
I appreciate your reply
March 4, 2012 at 5:45 am
Hello, its can do.
But what is your situation to do like this?
March 4, 2012 at 5:58 am
you are expecting this....????
USE tempdb;
CREATE TABLE #Blah
( C1 INT PRIMARY KEY,
C2 VARCHAR(1),
C3 VARCHAR(1),
C4 VARCHAR(1),
C5 VARCHAR(1),
C6 VARCHAR(1)
)
INSERT INTO #Blah VALUES('1', 'a', NULL, 'b', NULL, 'f')
INSERT INTO #Blah VALUES('2','d',NULL,'e', NULL,NULL)
INSERT INTO #Blah VALUES('3','g',NULL,'h',NULL,'h')
select C1,C2,C4,C6
FROM #Blah
March 4, 2012 at 6:00 am
Actually, this is not a data directly from the original table. This is a table variable populated from different tables based on a certain condition.After that I am not interested in the columns that have NULL values in all the records. Any possibility to reject columns with NULL value in all the records?
Thank U
March 4, 2012 at 6:04 am
The problem is, the situation is different for different condition. C1 may be NULL for one condition and may have value in other situation. The condition is dynamic.... it depends on the where condition that will populate the table variable. Any help?
March 4, 2012 at 6:11 am
Hi,
Can you please post some additional information..??? really i can't guess it..!!!
sorry...:-)
March 4, 2012 at 6:21 am
assefam (3/4/2012)
My Expected result will be ,remove Column C3 and C5 and returns the other as it isThe orginal value is
PKC1 C2 C3 C4 C5 C6
1 a NULL b NULL f
2 d NULL e NULL NULL
3 g NULL h NULL h
The expected result is
PKC1 C2 C4 C6
1 a b f
2 d e NULL
3 g h h
Just detect and remove all columns with no values at all ( columns with NULL values in all the records)
I appreciate your reply
are you allowed to use dynamic SQL?
will there only ever be 5 possible columns to base selection on?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 4, 2012 at 7:40 am
For security reason( SQL Injection),Dynamic SQL is not allowed for me.
The number of columns from the base table is known. But the number of columns having at least one NULL Value is not fixed ( depends on the where condition on the base table).
March 4, 2012 at 8:34 am
Since we're not allow to use Dynamic SQL, we need to do it in a fairly odd way. I originally got this idea from Jonathan Livingston SQL on a different thread.
Before we get started, I'd recommend that your management carefully reconsider the use of Dynamic SQL. Any SQL that [font="Arial Black"]isn't[/font] taking inputs from users can't suffer from SQL Injection. Any SQL that [font="Arial Black"]is[/font] taking inputs from users can easily be protected from SQL Injection. It's a real shame to let such FUD prevent the use of such a valuable tool as Dynamic SQL.
OK. I'm off the soapbox. Here's the setup for the SampleTable. The data only had a PK and columns C1 thru C5 so that's the way I made the SampleTable.
--===== Do this test in a nice, safe place that everyone has
USE tempdb
;
--===== Conditionally drop the test table to make reruns in SSMS easier.
IF OBJECT_ID('tempdb.dbo.SampleTable','U') IS NOT NULL
DROP TABLE tempdb.dbo.SampleTable
;
CREATE TABLE dbo.SampleTable
(
PK INT PRIMARY KEY CLUSTERED,
C1 VARCHAR(10),
C2 VARCHAR(10),
C3 VARCHAR(10),
C4 VARCHAR(10),
C5 VARCHAR(10)
)
;
--===== Populate the test table with data
INSERT INTO dbo.SampleTable
(PK,C1,C2,C3,C4,C5)
SELECT 1,'a',NULL,'b',NULL,'f' UNION ALL
SELECT 2,'d',NULL,'e',NULL,NULL UNION ALL
SELECT 3,'g',NULL,'h',NULL,'h'
;
Here's one possible solution with props for the idea going to Jonathan Livingston SQL. This is done without any type of Dynamic SQL. I hope your over-nervous management actually allows you to use a Temp Table. 😉
--===== Conditionally drop the working table to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#MyHead','U') IS NOT NULL
DROP TABLE #MyHead
;
--===== Copy the data from the original table to a temp table.
SELECT *
INTO #MyHead
FROM dbo.SampleTable
;
--===== Declare variables to hold counts of columns
DECLARE @Total INT,
@C1Count INT,
@C2Count INT,
@C3Count INT,
@C4Count INT,
@C5Count INT
;
--===== Get the non-null counts for each column and the table
SELECT @Total = COUNT(*),
@C1Count = COUNT(C1),
@C2Count = COUNT(C2),
@C3Count = COUNT(C3),
@C4Count = COUNT(C4),
@C5Count = COUNT(C5)
FROM #MyHead
;
--===== Dispose of any columns where the count is 0
IF @C1Count = 0 ALTER TABLE #MyHead DROP COLUMN C1;
IF @C2Count = 0 ALTER TABLE #MyHead DROP COLUMN C2;
IF @C3Count = 0 ALTER TABLE #MyHead DROP COLUMN C3;
IF @C4Count = 0 ALTER TABLE #MyHead DROP COLUMN C4;
IF @C5Count = 0 ALTER TABLE #MyHead DROP COLUMN C5;
--===== Display the correct result
SELECT *
FROM #MyHead
;
Here are the results... as expected.
Warning: Null value is eliminated by an aggregate or other SET operation.
PK C1 C3 C5
----------- ---------- ---------- ----------
1 a b f
2 d e NULL
3 g h h
(3 row(s) affected)
The problem in the future will be that any reference to a dropped column will cause a "column not found" error. THAT's because your management doesn't allow for the use of intelligent Dynamic SQL. 😉
Because of the requirements given for this problem, I'm also concerned about the proper design and use of such a table. Other than a Staging Table for loading a file, having the possibility of all-null columns generally means a design error that doesn't meet even the minimal requirements of First Normal Form.
--Jeff Moden
March 4, 2012 at 8:50 am
abhilasht (3/4/2012)
Hello, its can do.
Forget what it's for. Let's see it. 🙂
--Jeff Moden
March 4, 2012 at 11:18 am
Really thank you very much for your best explanation. I am satisfied using temp table.
how to solve this problem using dynamic SQL? Thank U
March 4, 2012 at 11:40 am
assefam (3/4/2012)
Really thank you very much for your best explanation. I am satisfied using temp table.how to solve this problem using dynamic SQL? Thank U
You're welcome.
Before I set to writing it, will you actually be able to use a Dynamic SQL solution or are you looking for something to help your management see that not all Dynamic SQL can suffer SQL Injection?
--Jeff Moden
March 5, 2012 at 4:18 am
I was discussing this issue with the management..........and I want to solve the problem with Dynamic SQL solution. Thank you Very much.
Viewing 15 posts - 1 through 15 (of 49 total)
You must be logged in to reply to this topic. Login to reply