How can I select from list of values in SQL Server

Question

Available only on SQL Server 2008 and over is row-constructor in this form:
You could use

SELECT DISTINCT *
FROM (
  VALUES (1), (1), (1), (2), (5), (1), (6)
) AS X(a)

For more information see:

• MS official
• http://www.sql-server-helper.com/sql-server-2008/row-value-constructor-as-derived-table.aspx

In general :

SELECT 
  DISTINCT 
      FieldName1, FieldName2, ..., FieldNameN
FROM
  (
    Values
        ( ValueForField1, ValueForField2,..., ValueForFieldN ),
        ( ValueForField1, ValueForField2,..., ValueForFieldN ),
        ( ValueForField1, ValueForField2,..., ValueForFieldN ),
        ( ValueForField1, ValueForField2,..., ValueForFieldN ),
        ( ValueForField1, ValueForField2,..., ValueForFieldN )
  ) AS TempTableName ( FieldName1, FieldName2, ..., FieldNameN )

In your case :

Select 
  distinct
  TempTableName.Field1 
From 
  (
  VALUES
    (1), 
    (1), 
    (1), 
    (2), 
    (5), 
    (1), 
    (6)
  ) AS TempTableName (Field1)

Simplest way to get the distinct values of a long list of comma delimited text would be to use a find an replace with UNION to get the distinct values.

SELECT 1
UNION SELECT 1
UNION SELECT 1
UNION SELECT 2
UNION SELECT 5
UNION SELECT 1
UNION SELECT 6

Applied to your long line of comma delimited text

• Find and replace every comma with UNION SELECT
• Add a SELECT in front of the statement

You now should have a working query

Have you tried using the following syntax?

select * from (values (1), (2), (3), (4), (5)) numbers(number)