Different ways of returning hard coded values via SQL

Question

I have some data that is hard coded in my select query.

The SQL is as follows:

SELECT 
'ZZ0027674',
'ZZ0027704',
'ZZ0027707',
'ZZ0027709',
'ZZ0027729',
'ZZ0027742',
'ZZ0027750'

Unfortunately it does not display the data. It just returns 7 columns and each column has each value. I just want 1 column with the different values.

Please provide me different solutions to display the data?

Solution 1

You can use VALUES, aka Table Value Constructor, clause for hardcoded values:

SELECT *
FROM (VALUES('ZZ0027674'),('ZZ0027704'),('ZZ0027707'),
            ('ZZ0027709'),('ZZ0027729'),('ZZ0027742'),
             ('ZZ0027750')
     ) AS sub(c)

LiveDemo

Warning: This has limitation up to 1000 rows and applies to SQL Server 2008+. For lower version you could use UNION ALL instead.

EDIT:

Extra points if someone can show me unpivot ?

SELECT col
FROM (SELECT 'ZZ0027674','ZZ0027704','ZZ0027707',
             'ZZ0027709','ZZ0027729','ZZ0027742','ZZ0027750'
     ) AS sub(v1,v2,v3,v4,v5,v6,v7)
UNPIVOT
(
   col for c in (v1,v2,v3,v4,v5,v6,v7)
) AS unpv;

LiveDemo2

Solution 2

Use union:

  SELECT 
'ZZ0027674' union all
SELECT 'ZZ0027704' union all
SELECT 'ZZ0027707' union all
SELECT 'ZZ0027709' union all
SELECT 'ZZ0027729' union all
SELECT 'ZZ0027742' union all
SELECT 'ZZ0027750'

Solution 3

Can also use union

SELECT 'ZZ0027674' as [col1] 
UNION 
SELECT 'ZZ0027704'

Comments

[Horaciux]

Only for SQL Server 2008 and above.

[Horaciux]

All SQL Server version compatible

[Lukasz Szozda]

@Horaciux Yes, added note about it.