'How to select multiple rows filled with constants?
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.
Solution 1:[1]
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 <= :n
in Oracle,
WITH q (l) AS
(
SELECT 1
UNION ALL
SELECT l + 1
FROM q
WHERE l < @n
)
SELECT l
FROM q
-- OPTION (MAXRECURSION 0)
-- uncomment line above if @n >= 100
in SQL Server,
SELECT l
FROM generate_series(1, $n) l
in PostgreSQL.
Solution 2:[2]
For Microsoft SQL Server or PostgreSQL you may want to try this syntax
SELECT constants FROM (VALUES ('[email protected]'), ('[email protected]'), ('[email protected]')) AS MyTable(constants)
You can also view an SQL Fiddle here: http://www.sqlfiddle.com/#!17/9eecb/34703/0
Solution 3:[3]
The following bare VALUES command works for me in PostgreSQL:
VALUES (1,2,3), (4,5,6), (7,8,9)
Solution 4:[4]
Try the connect by clause in oracle, something like this
select level,level+1,level+2 from dual connect by level <=3;
For more information on connect by clause follow this link : removed URL because oraclebin site is now malicious.
Solution 5:[5]
Oracle. Thanks to this post PL/SQL - Use "List" 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
Solution 6:[6]
SELECT *
FROM DUAL
CONNECT BY ROWNUM <= 9;
Solution 7:[7]
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('
<ROWSET>
<R><ID>1</ID><NAME>key1</NAME><VAL>value1</VAL></R>
<R><ID>2</ID><NAME>key2</NAME><VAL>value2</VAL></R>
<R><ID>3</ID><NAME>key3</NAME><VAL>value3</VAL></R>
</ROWSET>
') 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.
Solution 8:[8]
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
Solution 9:[9]
In Oracle
SELECT
CASE
WHEN level = 1
THEN 'HI'
WHEN level = 2
THEN 'BYE'
END TEST
FROM dual
CONNECT BY level <= 2;
Solution 10:[10]
In MySQL, you can do: values (1,2), (3, 4);
mysql> 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> SELECT * FROM (SELECT 1, 2, 3, 4) AS dt (a, b, c, d);
+---+---+---+---+
| a | b | c | d |
+---+---+---+---+
| 1 | 2 | 3 | 4 |
+---+---+---+---+
Solution 11:[11]
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
Solution 12:[12]
Here is how to do it using the XML features of DB2
SELECT *
FROM
XMLTABLE ('$doc/ROWSET/ROW' PASSING XMLPARSE ( DOCUMENT '
<ROWSET>
<ROW>
<A val="1" /> <B val="2" /> <C val="3" />
</ROW>
<ROW>
<A val="4" /> <B val="5" /> <C val="6" />
</ROW>
<ROW>
<A val="7" /> <B val="8" /> <C val="9" />
</ROW>
</ROWSET>
') AS "doc"
COLUMNS
"A" INT PATH 'A/@val',
"B" INT PATH 'B/@val',
"C" INT PATH 'C/@val'
)
AS X
;
Solution 13:[13]
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.
Solution 14:[14]
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 <= 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 <= 3;
Solution 15:[15]
With SQLite3, the following select Go .QueryContext()
VALUES (1), (2)
Returns a single untyped column named “Column1” and two rows with values 1 and 2
VALUES is a special form of the simple SELECT statement https://www.sqlite.org/lang_select.html#simpleselect
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow