How can I select from list of values in SQL Server

Question

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.

Answer 1

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

Answer 2

Have you tried using the following syntax?

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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