How to select several hardcoded SQL rows?

If you execute this query

SELECT 'test-a1' AS name1, 'test-a2' AS name2

the result will be a one row-selection with two columns having these values:

test-a1, test-a2

How can I modify the above query to have a selection with several rows, e.g.

test-a1, test-a2
test-b1, test-b2
test-c1, test-c2

I know how to do this with UNION but I feel that there exists a more simple way to do it.

PS. Sorry for such a basic question, it is very hard to google it.

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Values keyword can be used as below.

select * from 
(values ('test-a1', 'test-a2'), ('test-b1', 'test-b2'), ('test-c1', 'test-c2')) x(col1, col2)

Method 2

The following will work for SQL:

SELECT 'test-a1' AS name1, 'test-a2' AS name2 
UNION ALL 
SELECT 'test-b1', 'test-b2'
UNION ALL 
SELECT 'test-c1', 'test-c2'

Method 3

UNION ALL is the best bet. It’s faster than UNION and you will have mutually exclusive rows.

Method 4

Extending the answer of @openshac for oracle, as the below mentioned code works for oracle:

SELECT 'test-a1' AS name1, 'test-a2' AS name2 from dual 
UNION ALL 
SELECT 'test-b1', 'test-b2' from dual
UNION ALL 
SELECT 'test-c1', 'test-c2' from dual

Method 5

You can use a temp table, fill it up with your results and then select from it

create table #tmpAAA (name1 varchar(10), name2 varchar(10))
insert into #tmpAAA (name1, name2) 
values ('test_a', 'test_b'),
       ('test_c', 'test_d'),
       ('test_e', 'test_f'),
       ('test_g', 'test_h'),
       ('test_i', 'test_j');
select * from #tmpAAA;

This will return

name1   name2
==================
test_a  test_b
test_c  test_d
test_e  test_f
test_g  test_h
test_i  test_j

Method 6

I’d love to hear is anyone has a better solution. In the past I’ve used this:

Select top 3 'Hardcode'
from tableWithLotsOfRows

Would you mind switching abc, with 123?

select top 3 
    'test-A'+convert(varchar, row_number() over (order by PrimaryKey)),
    'test-B'+convert(varchar, row_number() over (order by PrimaryKey))
from tableWithLotsOfRows

that should return something like:

TestA1, Test-B1
TestA2, Test-B2
TestA3, Test-B3

Method 7

As of MySQL 8.0.19, it is possible to do

SELECT
    column_0 AS name1,
    column_1 AS name2
FROM
    (VALUES
        ROW('test-a1','test-a2'),
        ROW('test-b1','test-b2'),
        ROW('test-c1','test-c2')
    ) AS hardcodedNames

Which returns

name1   name2
==================
test-a1 test-a2
test-b1 test-b2
test-c1 test-c2

A note on column names

The columns of the table output from VALUES have the implicitly named columns column_0, column_1, column_2, and so on, always beginning with 0.

Documentation here: https://dev.mysql.com/doc/refman/8.0/en/values.html.

Method 8

In MySQL you could use UNION like this:

SELECT * from 
    (SELECT 2006 AS year UNION
     SELECT 2007 AS year UNION
     SELECT 2008 AS year UNION
    ) AS years

Method 9

The following code work for me in MSSQL environment:

SELECT Name1,Name2 FROM(VALUES  ('test-a1', 'test-a2'),
                                   ('test-b1', 'test-b2'),
                                   ('test-c1', 'test-c2'))AS Test(Name1,Name2)

Output:

Name1   Name2
------- -------
test-a1 test-a2
test-b1 test-b2
test-c1 test-c2

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating