Post reply

Simple derived table with multiple rows

  • May 10, 2013 at 9:24 am

    #277711

    I'm having trouble creating a simple derived table. It is so simple to create a derived table with multiple columns with:

    SELECT 1,2,3

    Feels like it shouldn't be much more difficult to create a derived table with one column and multiple tuples/rows.

    Is there a way to get following result without creating a temp table or specifying the whole DDL:

    derived table

    1

    2

    3

    Thanks

  • May 10, 2013 at 9:39 am

    #1614233

    Two possibilities, I'm sure there are more

    SELECT 1 AS col

    UNION ALL

    SELECT 2

    UNION ALL

    SELECT 3

    SELECT col

    FROM (VALUES (1), (2), (3)) x(col)

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • May 10, 2013 at 9:39 am

    #1614234

    memymasta (5/10/2013)

    I'm having trouble creating a simple derived table. It is so simple to create a derived table with multiple columns with:

    SELECT 1,2,3

    Feels like it shouldn't be much more difficult to create a derived table with one column and multiple tuples/rows.

    Is there a way to get following result without creating a temp table or specifying the whole DDL:

    derived table

    1

    2

    3

    Thanks

    There is a limit to how many rows you can do this way, but here is one:

    select * from

    (select n from (values (1),(2),(3))dt(n))dt1;

  • May 10, 2013 at 4:56 pm

    #1614348

    memymasta (5/10/2013)

    I'm having trouble creating a simple derived table. It is so simple to create a derived table with multiple columns with:

    SELECT 1,2,3

    Feels like it shouldn't be much more difficult to create a derived table with one column and multiple tuples/rows.

    Is there a way to get following result without creating a temp table or specifying the whole DDL:

    derived table

    1

    2

    3

    Thanks

    Now that the others have answered, let me ask...

    I notice the numbers are sequential. Is that just by coincidence or do you really need a derived table of sequential numbers?

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

  • May 13, 2013 at 8:09 am

    #1614610

    Thanks for the answers, seems like Mark-101232 had the shortest one.

    SELECT col FROM (VALUES (1), (2), (3)) x(col)

    @jeff Moden - Not really, they just happens to be sequential, we use a fixed number of periods (3), that are not in the DB. I needed to join those with another table to get a desired result.

    Your interest in sequential numbers, were you thinking of a tally table?

Viewing 5 posts - 1 through 4 (of 4 total)

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