Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: exluding rows from display

    if i have a table called tb1 and i want to display all of its columns except 1 say colExcl, is there a short cut way of doing this rather than enumerating all the rows I want included instead?
    TIA

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No. Aren't you mixing up rows and columns here too?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2006
    Posts
    111

    ...

    no i meant for example i have a table tab1 which has a 100 columns from col1 to col100


    if i want my result set to display columns 1, 5 and 7 it'd look like this:

    Code:
    select
       col1, col5, col7
    from
       tab1
    had i wanted to include all columns except those three i would have to enumerate all the columns in my select clause except those three as in select col2, col3, col4, col6, col8, col9, ..., col 100 ? is there a short hand way of excluding those columns when there are less to exclude?

    TIA

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    pootle flump has a good point about confusing rows and columns, but I'll take your original question as literally as I can...

    If I wanted to exclude one row, then I'd use NOT inside the WHERE clause, and just identify the row I intended to exclude.

    -PatP

  5. #5
    Join Date
    Jul 2006
    Posts
    111
    i wish there was a ranking and reward system for replies here like in the other forum i frequently visit so users can show how they appreciate the answers

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Drat, two posts, crossing in the night.

    First of all, you want to explicitly list the columns you want to return anyway. Using SELECT * is Ok when the DBA is just "data spelunking" and it is glorious inside the EXISTS clause, but in application code it should generally be avoided. This is oodles safer than trusting that some bozo like me won't come along and add/deleted/rename a column on you.

    -PatP

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeah - your title was "excluding rows from display" and you mentyioned rows again in your question.

    There is no such thing as "SELECT *...EXCEPT col1, col4...".

    This is something I use to generate a list of columns - delete the ones you don't want. SQLPrompt also allows you to do this.
    Code:
    USE <my_db>
    GO
    
    DECLARE    @t_name AS SYSNAME
            , @sql    AS VARCHAR(MAX)
    
    SELECT    @t_name = N'%myTName%'
    
    SELECT    @sql = COALESCE(@sql + ', ', '') + c.name 
    FROM    sys.all_columns AS c
    INNER JOIN 
            sys.all_objects AS o
    ON    c.object_id = o.object_id
    WHERE    o.name LIKE @t_name
    ORDER BY o.name
            , c.column_id
    
    PRINT    @sql
    Hmmm. I might revisit that code actually - I wrote it when I first got my hands on 2005.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rewrite it to use INFORMATION_SCHEMA views and it'll be portable to other databases, too

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    rewrite it to use INFORMATION_SCHEMA views and it'll be portable to other databases, too
    Your wish is my command, chuck. Got rid of the LIKE business too.

    Code:
    USE <my_db>
    GO
    
    DECLARE   @t_name AS SYSNAME
            , @s_name AS SYSNAME
            , @sql    AS VARCHAR(MAX)
    
    SELECT    @t_name = N'tName'
            , @s_name = N'schemaName'
    
    SELECT      @sql = COALESCE(@sql + ', ', '') + c.COLUMN_NAME 
    FROM    INFORMATION_SCHEMA.COLUMNS AS c
    WHERE   c.TABLE_NAME        = @t_name
            AND c.TABLE_SCHEMA    = @s_name
    ORDER BY c.TABLE_NAME
            , c.ORDINAL_POSITION
    
    PRINT    @sql
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I like poots' new version, but I'd be really surprised if the Coaleasce() trick is portable to anything other than SQL Server.

    -PatP

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pat, the technique works fine in mysql, but of course that nasty sql server "add strings" operator will need to be replaced in any other database system with the proper concatenation operator

    usually, this is the standard sql double pipes operator, but, sadly, in mysql it requires the CONCAT function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Any good reason || is concatenate in ANSI SQL? Especially since it is OR in a load of front endies.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    its etymology traces back to a language called PL/I (according to Joe Celko, SQL for Smarties, 3rd edition, p. 173)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Coo. Ta

    I've only read that book (or started reading it - might not have made it to p173).
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •