Different ways of returning hard coded values via SQL [duplicate]

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?

Answer 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

Answer 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'