Post reply

Select columns dynamically

  • March 3, 2012 at 1:21 pm

    #257820

    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

    #1454496

    Wats your expected result from that sample table?

  • March 4, 2012 at 4:57 am

    #1454544

    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

    #1454546

    Hello, its can do.

    But what is your situation to do like this?

  • March 4, 2012 at 5:58 am

    #1454548

    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

    #1454550

    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

    #1454551

    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

    #1454552

    Hi,

    Can you please post some additional information..??? really i can't guess it..!!!

    sorry...:-)

  • March 4, 2012 at 6:21 am

    #1454553

    assefam (3/4/2012)

    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

    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

    #1454559

    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

    #1454563

    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

    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)

  • March 4, 2012 at 8:50 am

    #1454565

    abhilasht (3/4/2012)

    Hello, its can do.

    Forget what it's for. Let's see it. 🙂

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

  • March 4, 2012 at 11:18 am

    #1454581

    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

    #1454589

    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

    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)

  • March 5, 2012 at 4:18 am

    #1454719

    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