CODEWITHSUNDEEP
sqlsql-servergroup-bydistinct
russds
russds

Reputation: 875

getting results of distinct columns in to a single column

I have a table which has:

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

What I want is a single list of distinct names, like:

col1
Smith, John
Robinson, Bob
Jones, Michael

I've tried a union doing:

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

But that doesn't give me the clastname and cfirstname 's.

I've also tried some various attempts at using distinct and group by but haven't gotten anything to work.

Any advice or direction is much appreciated!

Upvotes: 2

Views: 65

Answers (2)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16894

I checked your query and it work fine http://sqlfiddle.com/#!3/bac7f/4
but suggest to use the separation of each row with four columns into two rows with two columns.

Example on SQLServer2008+

SELECT DISTINCT o.lastname + ', ' + o.firstname AS name
FROM dbo.test142 t 
  CROSS APPLY(
              VALUES(Plastname, Pfirstname),
                    (Clastname, Cfirstname)
              ) o(lastname, firstname)

See demo on SQLFiddle

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460038

Maybe with a subquery which concats all four columns as pairs and a GROUP BY on the result:

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

or even easier: replace the UNION ALL with UNION which eliminates duplicates:

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

DEMO

Upvotes: 3

Related Questions