Post reply

condensed rows

  • July 21, 2009 at 3:13 pm

    #133199

    hi all -

    what is the best way to write the select statement below?

    im trying to return a single row from a table that contains 3 rows by eliminating the nulls

    example code:

    create TABLE x (col1 varchar(5), col2 varchar(5), col3 varchar(5),col4 int)

    insert x select '111',null,null,1

    insert x select null,'222',null,1

    insert x select null,null,'333',1

    SELECT

    (select col1 A from x where col1 is not null) A,

    (select col2 B from x where col2 is not null) B,

    (select col3 C from x where col3 is not null) C

    thanks!

  • July 21, 2009 at 3:23 pm

    #1027957

    Based on your sample data the following code should give you the same result as your code does.

    However, it will give you the max value for each of the three colums, if there will be more than one column NOT NULL.

    May I ask for you business rule behind it? It looks like a rather strange requirement since you're talking about a table with columns containig just one value each... :ermm:

    SELECT

    MAX (col1) AS A,

    MAX (col2) AS B,

    MAX (col3) AS C

    from x


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

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