如何将两列中的不同值选为一列

有一种方法:

select string_Agg(Location,',') as distinct_location_list
 from (
select A_LOC Location FROM tablename
union 
select Z_LOC Location FROM tablename
) tt

我想,我有一个快速的解决方案给你。但是,您可以优化它,也可以用不同的方法来实现它。

DECLARE @YourTable TABLE
(
Service_No VARCHAR(30),
A_LOC  VARCHAR(30),
Z_LOC  VARCHAR(30)
);
INSERT INTO  @YourTable Values('001','A','B');
INSERT INTO  @YourTable Values('002','A','C');
INSERT INTO  @YourTable Values('003',NULL,'C');
INSERT INTO  @YourTable Values('004','F','B');

DECLARE @TempTable TABLE(FINALDATA  VARCHAR(30));

INSERT INTO @TempTable
SELECT A_LOC FROM @YourTable WHERE A_LOC IS NOT NULL;
INSERT INTO @TempTable
SELECT Z_LOC FROM @YourTable WHERE Z_LOC IS NOT NULL;

SELECT DISTINCT FINALDATA FROM @TempTable;

注: 这段代码是用SQL SERVER编写的。请检查代码并告诉我。

或者,如果string\u agg函数不可用,请尝试此方法。

select 
  STUFF(
         (SELECT ', ' + Loc FROM 
            (
            select distinct A_Loc  as Loc from @t 
            union 
            select distinct Z_Loc  as Loc from @t 
            ) t2 
          FOR XML PATH (''))
        , 1, 1, '') 

我建议使用 apply 然后过滤和 distinct 惯性导航与制导:

select string_agg(loc, ',')
from (select distinct loc
      from t cross apply
           (values (a_loc), (z_loc)) v(loc)
      where loc is not null
     ) v;