[sql-server] How can I select from list of values in SQL Server

I have very simple problem that I can't solve. I need to do something like this:

select distinct * from (1, 1, 1, 2, 5, 1, 6).

Anybody can help??

Edit

The data comes as a text file from one of our clients. It's totally unformatted (it's a single, very long line of text), but it may be possible to do so in Excel. But it's not practical for me, because I will need to use these values in my sql query. It's not convenient to do so every time I need to run a query.

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

Another way that you can use is a query like this:

SELECT DISTINCT
    LTRIM(m.n.value('.[1]','varchar(8000)')) as columnName
FROM 
    (SELECT CAST('<XMLRoot><RowData>' + REPLACE(t.val,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
     FROM (SELECT '1, 1, 1, 2, 5, 1, 6') AS t(val)
    ) dt
  CROSS APPLY 
    x.nodes('/XMLRoot/RowData') m(n);

Have you tried using the following syntax?

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

Select user id from list of user id:

SELECT * FROM my_table WHERE user_id IN (1,3,5,7,9,4);

This works on SQL Server 2005 and if there is maximal number:

SELECT * 
FROM
  (SELECT ROW_NUMBER() OVER(ORDER BY a.id) NUMBER
  FROM syscomments a
  CROSS JOIN syscomments b) c
WHERE c.NUMBER IN (1,4,6,7,9)

If you need an array, separate the array columns with a comma:

SELECT * FROM (VALUES('WOMENS'),('MENS'),('CHILDRENS')) as X([Attribute])
,(VALUES(742),(318)) AS z([StoreID])

If you want to select only certain values from a single table you can try this

select distinct(*) from table_name where table_field in (1,1,2,3,4,5)

eg:

select first_name,phone_number from telephone_list where district id in (1,2,5,7,8,9)

if you want to select from multiple tables then you must go for UNION.

If you just want to select the values 1, 1, 1, 2, 5, 1, 6 then you must do this

select 1 
union select 1 
union select 1 
union select 2 
union select 5 
union select 1 
union select 6

PostgreSQL gives you 2 ways of doing this:

SELECT DISTINCT * FROM (VALUES('a'),('b'),('a'),('v')) AS tbl(col1)

or

SELECT DISTINCT * FROM (select unnest(array['a','b', 'a','v'])) AS tbl(col1)

using array approach you can also do something like this:

SELECT DISTINCT * FROM (select unnest(string_to_array('a;b;c;d;e;f;a;b;d', ';'))) AS tbl(col1)

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)

Use the SQL In function

Something like this:

SELECT * FROM mytable WHERE:
"VALUE" In (1,2,3,7,90,500)

Works a treat in ArcGIS

A technique that has worked for me is to query a table that you know has a large amount of records in it, including just the Row_Number field in your result

Select Top 10000 Row_Number() OVER (Order by fieldintable) As 'recnum' From largetable

will return a result set of 10000 records from 1 to 10000, use this within another query to give you the desired results

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)

Many wrote about, among them:

• [MS official] https://docs.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql
• http://www.sql-server-helper.com/sql-server-2008/row-value-constructor-as-derived-table.aspx

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',',')