Is it possible to Filter out Columns in Select statement?

  • We can filter Rows with WHERE clauses and JOINS etc
    But is there an easy way to filter Columns

    I was wondering if some feature of T-SQL would simplify this situation

    IF @ExtendedParam = 1
    BEGIN
         SELECT A, B, C, D, E, F 
         FROM MyTable
    END
    ELSE IF @ExtendedParam = 2
    BEGIN
        SELECT A,B,C
        FROM MyTable
    END
    ELSE IF @ExtendedParam = 3 
     ..... 

    I have tried variations on 

    SELECT A, B, C
        ,CASE WHEN @ExtendedParam = 1 THEN D
        END
        ,CASE WHEN @ExtendedParam = 1 THEN E
        END
        ,CASE WHEN @ExtendedParam = 1 THEN F
        END
    FROM @MyTable

    This is no good to me because it outputs the columns I want to exclude from the results

    I have not found a way of filtering on Columns without creating a separate SELECT statement for each variation of the filter

    In the real query @ExtendedParam has (currently) 8 values - meaning I have 8 copies of the 'SELECT A, B, C' part of the query. And that is actually quite complex with 35 columns and various formatting functions and case statements of its own, so it would be really useful not to have to repeat it 8 times!

  • You might have to use dynamic SQL to achieve this.

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • Why would you want to return a different set of data?  This type of output could not bind to external applications like SSRS/SSIS because the system would not be able to determine what columns would be returned.

    Another option would be to return a column that identified what type of data is being returned - and then populate the extra column with the appropriate data.

    SELECT A, B, C, ExtendedParam = @ExtendedParam
      ,ParamValue = CASE WHEN @ExtendedParam = 1 THEN D
                         WHEN @ExtendedParam = 2 THEN E
                         WHEN @ExtendedParam = 3 THEN F
      END
    FROM @MyTable

    If there are other column values to be returned for different conditions:

    SELECT A, B, C, ExtendedParam = @ExtendedParam
      ,ParamValue = CASE WHEN @ExtendedParam = 1 THEN D
                         WHEN @ExtendedParam = 2 THEN E
                         WHEN @ExtendedParam = 3 THEN F
      END
      ,ParamValue1 = CASE WHEN @ExtendedParam = 1 THEN E END
      ,ParamValue2 = CASE WHEN @ExtendedParam = 1 THEN F END
    FROM @MyTable 

    With this you would always get all columns - but then only those columns that should be populated based on the parameters would have values.  Downstream systems can then evaluate the ExtendedParam value and choose what columns to display or ignore.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Happy New Year

    And thank-you for your suggestions.

    I Decided the Dynamic SQL route fitted my problem best.

    The results go to an Excel Report for the end user - not to any further processing 

    In the interest of separation of concerns I created a function for each variable set of columns 


    CREATE FUNCTION dbo.ufn_Param1 ()
    RETURNS NVARCHAR(MAX)
    AS
    BEGIN
     RETURN 'D, E, F'
    END
    GO
    CREATE FUNCTION dbo.ufn_Param2 ()
    RETURNS NVARCHAR(MAX)
    AS
    BEGIN
     RETURN 'D'
    END
    GO
    CREATE FUNCTION dbo.ufn_Param3 ()
    RETURNS NVARCHAR(MAX)
    AS
    BEGIN
     RETURN 'D, F'
    END
    GO
    GO
    CREATE FUNCTION dbo.ufn_ColList (@Param INT)
    RETURNS NVARCHAR(MAX)
    AS
    BEGIN
     DECLARE @RTN NVARCHAR(MAX);
     SELECT @RTN = CASE @Param
     WHEN 1 THEN dbo.ufn_Param1()
     WHEN 2 THEN dbo.ufn_Param2()
     WHEN 3 THEN dbo.ufn_Param3()
     END;

     RETURN @RTN;
    END
    GO

    Then the final query can be simplified to 


    DROP TABLE IF EXISTS ##MyTable;
    DECLARE @ExtendedParam INT;
    CREATE TABLE ##MyTable ( A INT, B INT, C INT, D VARCHAR(10), E VARCHAR(10), F VARCHAR(10) );
    INSERT INTO ##MyTable
    SELECT 1, 2, 3, 'D', 'E', 'F'

    DECLARE @SQL NVARCHAR(MAX);
    DECLARE @FixedCols NVARCHAR(MAX);
    SET @FixedCols = 'A, B, C';
    SET @ExtendedParam = 3;
    SET @SQL = 'SELECT ' + @FixedCols + ', ' + dbo.ufn_ColList(@ExtendedParam)+ ' FROM ##MyTable '
    EXEC sp_executesql @SQL

  • Fewer keystrokes:
    DECLARE @SQL NVARCHAR(MAX);
    DECLARE @ExtendedParam INT = 3;
    SET @SQL = 'SELECT A, B, C, ' + CASE
     WHEN @ExtendedParam = 1 THEN 'D, E, F'
     WHEN @ExtendedParam = 2 THEN 'D'
     WHEN @ExtendedParam = 3 THEN 'D, F'
     ELSE NULL END + ' FROM ##MyTable '
    PRINT @SQL
    β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • tom 69406 - Friday, December 21, 2018 9:24 AM

    Can you afford to buy a book on basic RDBMS? You have missed the part about what a table is. A table, by definition, must have a key and must be made up of a fixed known number of columns. Those columns have known data types. Then I want you to go back and get a book on basic software engineering. It's not a big fad it was decades ago, but it really is important to know if you're going to work in IT. It's based on two concepts that you can Google; coupling and cohesion. Coupling is how tightly the code modules in the system (regardless of language!) are tied together. Cohesion is how well a module does one and only one task, with one and only one entry point and one and only one exit point. Over the last 50 years. We have found that systems with loose coupling and high cohesion are easier to maintain and work better. You can Google all of this might want to spend some time reading the thousands and thousands of pages of literature and proof of the statement.

    What you are asking us to help you do is a design flaw called "flag coupling", which means that the way your module works is determined by an external source passing flags that are not known until runtime into it. This is considered the worst way to write code in any language and has been for about 40+ years.

    The way in SQL programmer would do this would be to write a single query that gives him all the information he needs and then he would "toss it over the relational wall" to the next layer of the tiered architecture. It would probably be a presentation layer, I'm guessing. Then, once it's over the wall, that layer will format and pretty-print it for display and probably take out some of the unneeded columns.

    One of the good tests, when you're designing a module of code, is to ask "what would a rational name for this module be?" If the name for this module includes something like "Britney Spears, Lady Gaga, automobiles and squids" in it, then it's probably doing too much. It's worse in the Swiss Army knife πŸ™

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • @joe
    [What you are asking us to help you do is a design flaw called "flag coupling", which means that the way your module works is determined by an external source passing flags that are not known until runtime into it. This is considered the worst way to write code in any language and has been for about 40+ years.]

    Wow I can see you are a purist programmer, who likes rules to be followed.  However unlike the immutable laws of physics, the rules of programming are just the opinions of other people, and so are pragmatic, flexible, and can be adapted by circumstances.  One person's design flaw is another person's practical solution. 

    And you appear to have overlooked the fact that I have simplified the code to show the essence of the problem I wanted help with

    The output is essentially a set of "what if" illustrations for the customer.  If my customer wants a report with the same first 30 columns and 8 additional column sets as optional variations on that report, I am pragmatic:  I don't tell her she is breaking the rules on what a table is,  and I don't repeat the code for the first 30 columns in 8 separate reports.  She gets the reports she wants by the quickest most efficient methods available to me.  

    As for getting the presentation layer to do the work - its too slow - I only pass the data requested - I can't assume they want the other 7 variations.  SQL Server is very good at this stuff, even if it can't cache or optimise dynamic queries - it still beats excessive network traffic and the presentation layer.

  • tom 69406 - Thursday, January 3, 2019 2:38 AM

    @joe
    [What you are asking us to help you do is a design flaw called "flag coupling", which means that the way your module works is determined by an external source passing flags that are not known until runtime into it. This is considered the worst way to write code in any language and has been for about 40+ years.]

    Wow I can see you are a purist programmer, who likes rules to be followed.  However unlike the immutable laws of physics, the rules of programming are just the opinions of other people, and so are pragmatic, flexible, and can be adapted by circumstances.  One person's design flaw is another person's practical solution. 

    And you appear to have overlooked the fact that I have simplified the code to show the essence of the problem I wanted help with

    The output is essentially a set of "what if" illustrations for the customer.  If my customer wants a report with the same first 30 columns and 8 additional column sets as optional variations on that report, I am pragmatic:  I don't tell her she is breaking the rules on what a table is,  and I don't repeat the code for the first 30 columns in 8 separate reports.  She gets the reports she wants by the quickest most efficient methods available to me.  

    As for getting the presentation layer to do the work - its too slow - I only pass the data requested - I can't assume they want the other 7 variations.  SQL Server is very good at this stuff, even if it can't cache or optimise dynamic queries - it still beats excessive network traffic and the presentation layer.

    Yep.... @joe is a real curmudgeon with no concern for practicality, or apparently, even the standards he glorifies.   He violates them at his convenience and then castigates others for the slightest infraction against them.  Best bet... ignore him.  It's so rare that he actually has something useful to offer, that's it not worth spending the time even reading his posts.   I had had enough of him a long time ago....  And I tired of his "history lessons" even longer ago.

Viewing 8 posts - 1 through 8 (of 8 total)

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