How to select multiple rows filled with constants

Question

Selecting constants without referring to a table is perfectly legal in an SQL statement   SELECT 1  2  3   The result set that the latter returns is a single row containing the values  I was wondering if there is a way to select multiple rows at once using a constant expression  something kind of   SELECT   1  2  3    4  5  6    7  8  9     I would want something like the above that works and returns a result set with 3 rows and 3 columns

User · Accepted Answer

SELECT 1  2  3 UNION ALL SELECT 4  5  6 UNION ALL SELECT 7  8  9

User · Answer

In PostgreSQL  you can do   SELECT    FROM              VALUES          1  2            3  4            AS q  col1  col2    In other systems  just use UNION ALL   SELECT  1 AS col1  2 AS col2 -- FROM    dual -- uncomment the line above if in Oracle UNION ALL SELECT  3 AS col1  3 AS col2 -- FROM    dual -- uncomment the line above if in Oracle   In Oracle  SQL Server and PostgreSQL  you also can generate recordsets of arbitrary number of rows  providable with an external variable    SELECT  level FROM    dual CONNECT BY         level  lt    n   in Oracle   WITH    q  l  AS                   SELECT  1         UNION ALL         SELECT  l   1         FROM    q         WHERE   l  lt   n           SELECT  l FROM    q -- OPTION  MAXRECURSION 0  -- uncomment line above if  n  gt   100   in SQL Server   SELECT  l FROM    generate series 1   n  l   in PostgreSQL

User · Answer

select  level - 1    row dif   1 as a   level - 1    row dif   2 as b   level - 1    row dif   3 as c     from dual      connect by level  lt   number of rows    something like that  select  level - 1    3   1 as a   level - 1    3   2 as b   level - 1    3   3 as c     from dual      connect by level  lt   3

User · Answer

Here is how to do it using the XML features of DB2   SELECT   FROM XMLTABLE    doc ROWSET ROW  PASSING XMLPARSE   DOCUMENT    lt ROWSET gt     lt ROW gt       lt A val  1    gt   lt B val  2    gt   lt C val  3    gt     lt  ROW gt     lt ROW gt       lt A val  4    gt   lt B val  5    gt   lt C val  6    gt     lt  ROW gt     lt ROW gt       lt A val  7    gt   lt B val  8    gt   lt C val  9    gt     lt  ROW gt   lt  ROWSET gt     AS  doc     COLUMNS         A  INT PATH  A  val          B  INT PATH  B  val          C  INT PATH  C  val     AS X

User · Answer

SELECT    FROM DUAL  CONNECT BY ROWNUM  lt   9

User · Answer

Try the connect by clause in oracle  something like this  select level level 1 level 2 from dual connect by level  lt  3    For more information on connect by clause follow this link   removed URL because oraclebin site is now malicious

User · Answer

In Oracle   SELECT   CASE     WHEN level   1     THEN  HI      WHEN level   2     THEN  BYE    END TEST FROM dual   CONNECT BY level  lt   2

User · Answer

This way can help you  SELECT   TOP 3          1 AS First            2 AS Second            3 AS Third  FROM     Any Table In Your DataBase   Any Table In Your DataBase  any table which contains more than 3 records  or use any system table  Here we have no concern with data of that table   You can bring variations in result set by concatenating a column with First  Second and Third columns from Any Table In Your DataBase table

User · Answer

For Microsoft SQL Server or PostgreSQL you may want to try this syntax  SELECT constants FROM  VALUES   foo gmail com      bar gmail com      baz gmail com    AS MyTable constants   You can also view an SQL Fiddle here  http   www sqlfiddle com   17 9eecb 34703 0

User · Answer

Oracle  Thanks to this post PL SQL - Use  quot List quot  Variable in Where In Clause  I put together my example statement to easily manually input values  being reused in testing an application by testers    WITH prods AS       SELECT column value AS prods code      FROM TABLE          sys odcivarchar2list               prod1                 prod2                    SELECT   FROM prods

User · Answer

The following bare VALUES command works for me in PostgreSQL  VALUES  1 2 3    4 5 6    7 8 9

User · Answer

In MySQL  you can do  values  1 2    3  4    mysql gt  values  1 2    3  4    --- ---    1   2    --- ---    1   2     3   4    --- ---  2 rows in set  0 004 sec    With MySQL 8  it is also possible to give the column names   mysql gt  SELECT   FROM  SELECT 1  2  3  4  AS dt  a  b  c  d    --- --- --- ---    a   b   c   d    --- --- --- ---    1   2   3   4    --- --- --- ---

User · Answer

Here is how I populate static data in Oracle 10  using a neat XML trick   create table prop  ID NUMBER   NAME varchar2 10    VAL varchar2 10    CREATED timestamp   CONSTRAINT PK PROP PRIMARY KEY ID      merge into Prop p using   select    extractValue value r     R ID   ID    extractValue value r     R NAME   NAME    extractValue value r     R VAL   VAL from  select xmltype    lt ROWSET gt      lt R gt  lt ID gt 1 lt  ID gt  lt NAME gt key1 lt  NAME gt  lt VAL gt value1 lt  VAL gt  lt  R gt      lt R gt  lt ID gt 2 lt  ID gt  lt NAME gt key2 lt  NAME gt  lt VAL gt value2 lt  VAL gt  lt  R gt      lt R gt  lt ID gt 3 lt  ID gt  lt NAME gt key3 lt  NAME gt  lt VAL gt value3 lt  VAL gt  lt  R gt   lt  ROWSET gt     xml from dual  input   table xmlsequence input xml extract   ROWSET R     r   p new on  p ID   p new ID  when not matched then insert  ID  NAME  VAL  CREATED  values   p new ID  p new NAME  p new VAL  SYSTIMESTAMP      The merge only inserts the rows that are missing in the original table  which is convenient if you want to rerun your insert script

User · Answer

Here a way to create custom rows directly with MySQL request SELECT   SELECT ALL   FROM       VALUES         ROW  1  2  3           ROW  4  5  6           ROW  7  8  9    AS dummy  c1  c2  c3   Gives us a table dummy   c1   c2   c3 -------------  1    2    3  4    5    6  7    8    9  Tested with MySQL 8

User · Answer

An option for DB2   SELECT 101 AS C1  102 AS C2 FROM SYSIBM SYSDUMMY1 UNION ALL SELECT 201 AS C1  202 AS C2 FROM SYSIBM SYSDUMMY1 UNION ALL SELECT 301 AS C1  302 AS C2 FROM SYSIBM SYSDUMMY1

[sql] How to select multiple rows filled with constants?

Examples related to sql

Examples related to select

Examples related to constants