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.

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)

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)

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)

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

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

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;

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);

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

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

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