sql - 将不同列的结果放入一列

Question

我有一张 table ，里面有:

col1     col2     ...     Plastname     Pfirstname     Clastname      Cfirstname
1        abc              Smith         John           Robinson       Bob
2        xyz              Smith         John           Jones          Michael
3        bda              Robinson      Bob            Smith          John

我想要的是一个不同名称的列表，例如:

col1
Smith, John
Robinson, Bob
Jones, Michael

我试过一个工会做:

select distinct Plastname + ', ' + Pfirstname as name from table
union
select distinct Clastname + ', ' + Cfirstname as name from table

但这并没有给我 clastname 和 cfirstname 的。

我还尝试了一些使用不同和分组的各种尝试，但没有任何工作。

非常感谢任何建议或方向!

Answer 1

也许有一个将所有四列成对连接的子查询和一个 GROUP BY结果:

SELECT X.NAME FROM
(
    SELECT Plastname + ', ' + Pfirstname AS NAME
    FROM People
    UNION ALL
    SELECT Clastname + ', ' + Cfirstname AS NAME
    FROM People
) X
GROUP BY X.NAME
ORDER BY X.NAME

DEMO

甚至 更容易 : 替换 UNION ALL与 UNION消除重复:

SELECT Plastname + ', ' + Pfirstname AS NAME
FROM People
UNION 
SELECT Clastname + ', ' + Cfirstname AS NAME
FROM People

DEMO