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.

do you want to select from multiple tables or select from a single table but having a specific values to select? some thing like specific id's alone — Anirudh Goel, Oct 14 '09 at 08:22
Not what you ask, but you can do it in another language. For example in PowerShell, you can do `$d = (1, 1, 1, 2, 5, 1, 6) | sort -Unique` to get the distinct values in an array `$d`. Easy to extend to a file-to-file tool. — Jeppe Stig Nielsen, Dec 11 '17 at 10:42
Is the important thing here to get a distinct list of those values, or to get that list of values into SQL? As @JeppeStigNielsen says, there are other ways to get distinct values from a text list that doesn't involve SQL. I came here looking for how to get a list of values into an SQL script that references other tables. — Rikki, Dec 10 '18 at 23:53
the VALUES ( (1), (2), (3) ) AS X(Value) is the correct answer, please update it — DATEx2, Oct 27 '20 at 10:24

score 618 · Answer 1 · edited Oct 19 '21 at 06:18

618

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:

edited Oct 19 '21 at 06:18

CervEd

3,306
28
25

answered Sep 02 '11 at 14:39

pm.

6,281
2
14
3

110

Side-note: `X` is alias for table name and `a` is alias for column name ;). – shA.t Dec 26 '15 at 07:19
23

This is the more correct answer compared to the one currently selected – TabsNotSpaces Mar 08 '17 at 18:38
1

This is the most generic way, I was spoiled by unnest(ARRAY[]) in `pgsql` and now banging head to make FROM accept minor values as row records in `sqlserver`, and here it is. Happy to know. – Ben Oct 23 '17 at 09:49
2

Better answer due to column and table alias – Alfredo A. May 02 '18 at 15:13

Ardalan Shahgholi · Answer 2 · 2018-05-30T17:42:07.053

142

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)

edited May 30 '18 at 17:42

answered Jan 29 '16 at 20:48

Ardalan Shahgholi

11,967
21
108
144

1

I know "select *" is considered bad form, but is there any reason not to use select * in this case? Because that duplication of FieldName1, FieldName2, ..., FieldNameN is grotesque. – Pxtl Jun 26 '20 at 19:10
@Pxtl There is no reason to not use "Select *". I have rewritten those field's names to be more clear. Also, you does not maybe need "Distinct" keyword . – Ardalan Shahgholi Jun 29 '20 at 12:39

Lieven Keersmaekers · Accepted Answer · 2016-12-13T17:04:00.050

105

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

edited Dec 13 '16 at 17:04

answered Oct 14 '09 at 08:24

Lieven Keersmaekers

57,207
13
112
146

9

no, no, I have a list of several hundreds of values, manually it would be torture – Eedoh Oct 14 '09 at 08:29
where does that list come from? It might be way easier to just copy /paste that list in Excel and extract the distinct values there using a simple crosstab. – Lieven Keersmaekers Oct 14 '09 at 08:30
1

btw, find and replace might also take you a long way. Replace every comma with *union select*, add a select in front and you should have a working query cfr the union I showed. – Lieven Keersmaekers Oct 14 '09 at 08:35
it comes as a text file from one of our clients. It's totally unformated (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 theese values in my sql query. It's not convenient to do so every time I need to run a query – Eedoh Oct 14 '09 at 08:35
2

this stuff with replacing commas with select union works like a charm Thanks a lot :) – Eedoh Oct 14 '09 at 08:41
5

For performance reasons, I'd recommend Union-All, then Group-By or use Distinct in your outer select. – MikeTeeVee May 12 '14 at 20:39
I should note that you need to have an alias for your column name at least in your first `SELECT` statement ;). – shA.t Dec 26 '15 at 07:17
Computers are torture. – Ian Warburton Mar 14 '18 at 15:33
Note that with Microsoft SQL Server 2008 the answer's example is removing duplicates. I get 4 rows (1, 2, 5, 6). – pyb Mar 15 '18 at 22:03
@pyb - it should remove duplicates in every version. That's the intent of OP. – Lieven Keersmaekers Nov 21 '18 at 12:32

score 47 · Answer 4 · edited Dec 12 '14 at 11:47

47

Have you tried using the following syntax?

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

edited Dec 12 '14 at 11:47

bummi

27,123
14
62
101

answered Dec 12 '14 at 11:45

Robba

7,684
12
48
76

6

an anonymous user suggested to edit the code to: `SELECT DISTINCT table_name.column_name FROM (VALUES (1), (2), (3)) AS table_name(column_name)` – Vogel612 Oct 22 '15 at 10:25

score 22 · Answer 5 · edited Jan 14 '15 at 17:19

22

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

edited Jan 14 '15 at 17:19

Ricardo Souza

16,030
6
37
69

answered Oct 14 '09 at 08:26

Anirudh Goel

4,571
19
79
109

1

I don't need to select from a table, but from this list of values (in brackets). That's the main problem (selecting from comma separated array of values, not from a table) – Eedoh Oct 14 '09 at 08:31
that case, like we have DUAL table in Oracle, you can make use of the same. But since there is no DUAL then you will have to go the union way. You can try another method, as you mentioned you have comma separated array of values, why don't you insert them to a table and then use a neat sql select query, instead of using so many sql unions. – Anirudh Goel Oct 14 '09 at 08:35

score 19 · Answer 6 · edited Aug 01 '12 at 20:14

19

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)

edited Aug 01 '12 at 20:14

Spudley

166,037
39
233
307

answered Aug 01 '12 at 20:00

Arek

191
1
2

11

Although the question does specify MSSQL... `:)` – halfer Aug 01 '12 at 20:20
@halfer The first answer given here worked for me, using MSSQL 2016 while the other answers didn't. 7 years later – dustytrash Sep 09 '19 at 13:48

score 10 · Answer 7 · answered Dec 26 '18 at 14:48

10

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

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

answered Dec 26 '18 at 14:48

NapkinBob

632
7
19

This seems to require MSSQL 2016 or later: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql – Jonathan Mar 13 '19 at 14:28
1

@Sam Yes, this is SQL Server, per the original question's tags – NapkinBob Apr 22 '19 at 22:31
3

@Jonathan Yes, given the age of the question, It wouldn't have helped the original poster, but I figured someone might stumble upon it, as I did, and find it helpful. – NapkinBob Apr 22 '19 at 22:32

score 8 · Answer 8 · answered Oct 14 '09 at 08:36

8

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)

answered Oct 14 '09 at 08:36

LukLed

31,452
17
82
107

2

+1 neat but it is limited to the amount of rows in syscomments cross joined with itself. In my case to 294849. (and you forgot distinct.) – Lieven Keersmaekers Oct 14 '09 at 08:40
You can cross join once more, but replacing commas is much faster solution. – LukLed Oct 14 '09 at 09:08
Yeah, this way is good also, but I prefer Lieven's solution, because of simplicity. – Eedoh Oct 14 '09 at 10:41

score 3 · Answer 9 · edited Sep 03 '21 at 18:20

3

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;

edited Sep 03 '21 at 18:20

Jeremy Caney

7,102
69
48
77

answered Sep 03 '21 at 13:57

ashkanyo

81
6

score 2 · Answer 10 · answered Sep 15 '17 at 16:48

2

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

answered Sep 15 '17 at 16:48

gilgarola

23
3

score 0 · Answer 11 · answered Dec 26 '15 at 07:34

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

score 0 · Answer 12 · answered Mar 07 '20 at 05:34

0

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

answered Mar 07 '20 at 05:34

Biplob Das

2,818
21
13

Denis P. · Answer 13 · 2020-11-18T06:45:11.713

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;

score 0 · Answer 14 · answered Dec 02 '20 at 19:24

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

score -3 · Answer 15 · edited Jan 08 '13 at 16:38

-3

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

edited Jan 08 '13 at 16:38

Brian Mains

50,520
35
148
257

answered Jan 08 '13 at 16:21

Jwalter

7

score -6 · Answer 16 · edited Dec 03 '13 at 00:10

-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

edited Dec 03 '13 at 00:10

Dan Is Fiddling By Firelight

5,981
17
77
128

answered Dec 02 '13 at 23:51

user3059384

1
1

1

This SELECT generates a syntax error with SQL Server. – JohnH Jan 03 '18 at 19:55

How can I select from list of values in SQL Server

16 Answers16

Linked