Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Simple derived table with multiple rows Expand / Collapse
Author
Message
Posted Friday, May 10, 2013 9:24 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 4, 2013 6:26 AM
Points: 99, Visits: 402
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
Post #1451642
Posted Friday, May 10, 2013 9:39 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:40 PM
Points: 1,683, Visits: 19,609
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)



____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1451646
Posted Friday, May 10, 2013 9:39 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:11 PM
Points: 20,861, Visits: 32,887
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;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1451647
Posted Friday, May 10, 2013 4:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1451780
Posted Monday, May 13, 2013 8:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 4, 2013 6:26 AM
Points: 99, Visits: 402
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?
Post #1452123
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse