CODEWITHSUNDEEP
sql-server
jozi
jozi

Reputation: 2851

Function for finding all distinct values in a table in SQL Server

I need a function for all distinct values in my table in SQL Server.

For example, if my table looks like this:

field1   | field2   | ... | field8
---------+----------+-----+---------
valuef11 | valuef21 | ... | valuef81
valuef12 | valuef22 | ... | valuef82
etc...

Then I need to return a table with 2 fields as follows:

field1 valuef11
field1 valuef12
field1 valuef13
field2 valuef21
field2 valuef22
....
field8 valuef81
field8 valuef82
field8 valuef83
field8 valuef84

Upvotes: 1

Views: 880

Answers (3)

Mark Byers
Mark Byers

Reputation: 838336

I think you are looking for this:

SELECT DISTINCT 'field1' AS fieldname, field1 FROM yourtable
UNION ALL
SELECT DISTINCT 'field2' AS fieldname, field2 FROM yourtable
UNION ALL
...
UNION ALL
SELECT DISTINCT 'field8' AS fieldname, field8 FROM yourtable

Note that this assumes that all fields have the same type.

In SQL Server 2005 or newer you could also use UNPIVOT:

SELECT DISTINCT fieldname, fieldvalue
FROM yourtable
UNPIVOT(fieldvalue FOR fieldname IN
   (field1, field2, ..., field8)
) AS unpvt;

Again, this assumes that all fields have the same type.

Upvotes: 2

Andomar
Andomar

Reputation: 238116

You could use a union wrapped in a distinct subquery:

select  distinct A
,       B
from    (   
        select field1 as A, field11 as B from YourTable
        union all
        select field1, field12 from YourTable
        union all
        select field1, field13 from YourTable
        union all
        select field2, field21 from YourTable
        union all
        select field2, field22 from YourTable
        ) as SubQueryAlias

If your first field is always 6 charachters long, and your second field always 7, you can generate a query like:

declare @sql varchar(max)

select  @sql = case 
            when @sql is null then 'select ' + a.name + ' as a, ' + b.name + 
                ' as b from TestTable '
            else @sql + 'union all select ' + a.name + ', ' + b.name + 
                ' from TestTable ' 
            end
from    sys.all_columns a
join    sys.all_columns b
on      len(b.name) = 7
        and substring(b.name,1,6) = a.name
        and b.object_id = a.object_id
where   a.object_id = object_id('TestTable')

set @sql = 'select distinct a, b from (' + @sql + ') as SubQueryAlias'

exec (@sql)

Upvotes: 1

DAEMYO
DAEMYO

Reputation: 999

you want to distinct based on 8 fields and show 2 fields.

You'll have repeating pairs if you do that. Is that what you want? or do you want to do a "Group By" clause?

Cheers!

Upvotes: 0

Related Questions