用于在SQL Server中查找表中所有不同值的函数

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

我需要一个函数用于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:

然后我需要返回一个包含2个字段的表,如下所示:

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

3 个解决方案

#1

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:

在SQL Server 2005或更高版本中,您还可以使用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.

同样,这假设所有字段都具有相同的类型。

#2

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:

如果您的第一个字段总是6个字符长,而您的第二个字段总是7个,则可以生成如下查询:

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)

#3

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

你想基于8个字段区分并显示2个字段。

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!

#1