How can I select from list of values in SQL Server

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)

I know this is a pretty old thread, but I was searching for something similar and came up with this.

Given that you had a comma-separated string, you could use `string_split`

select distinct value from string_split('1, 1, 1, 2, 5, 1, 6',',')

This should return

1
2
5
6

String split takes two parameters, the string input, and the separator character.

you can add an optional where statement using `value` as the column name

select distinct value from string_split('1, 1, 1, 2, 5, 1, 6',',')
where value > 1
produces

2
5
6

> **Select user id from list of user id:**
>
> SELECT * FROM my_table WHERE user_id IN (1,3,5,7,9,4);

If it is a list of parameters from existing SQL table, for example ID list from existing Table1, then you can try this:

select distinct ID
FROM Table1
where
ID in (1, 1, 1, 2, 5, 1, 6)
ORDER BY ID;

Or, if you need List of parameters as a SQL Table constant(variable), try this:

WITH Id_list AS (
select ID
FROM Table1
where
ID in (1, 1, 1, 2, 5, 1, 6)
)
SELECT distinct * FROM Id_list
ORDER BY ID;

I create a function on most SQL DB I work on to do just this.

CREATE OR ALTER FUNCTION [dbo].[UTIL_SplitList](@parList Varchar(MAX),@splitChar Varchar(1)=',')
Returns @t table (Column_Value varchar(MAX))
as
Begin
Declare @pos integer
set @pos = CharIndex(@splitChar, @parList)
while @pos > 0
Begin
Insert Into @t (Column_Value) VALUES (Left(@parList, @pos-1))
set @parList = Right(@parList, Len(@parList) - @pos)
set @pos = CharIndex(@splitChar, @parList)
End
Insert Into @t (Column_Value) VALUES (@parList)
Return
End

Once the function exists, it is as easy as


SELECT DISTINCT
*
FROM
[dbo].[UTIL_SplitList]('1,1,1,2,5,1,6',',')

Using `GROUP BY` gives you better performance than `DISTINCT`:

SELECT *
FROM
(
VALUES
(1),
(1),
(1),
(2),
(5),
(1),
(6)
) AS A (nums)
GROUP BY A.nums;

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](

[To see links please register here]

)
-

[To see links please register here]