How can I select from list of values in SQL Server
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)