CODEWITHSUNDEEP
sqlsql-serveruniquedistinctsql-server-2014
Dmitry
Dmitry

Reputation: 315

Unique results for each column in 1 table in SQL Server

Using SQL Server 2014, I have a really big table where I need to get something like list of unique items of each column, I don't need regard between each other. The same like if I will distinct each column in different tables. But i need to get result in one table.

Example of initial data:

 Project   | Status    | Employees |
 -----------------------------------
 Project 1 |   Active  |     10    |  
 Project 2 |   Closed  |     10    | 
 Project 3 |   Closed  |     20    | 
 Project 1 |   Active  |     20    | 
 Project 2 |   Closed  |     20    |

Requested data, where I need to get only unique results of each column.

 Project   | Status    | Employees | 
 -----------------------------------
 Project 1 |   Active  |     10    |  
 Project 2 |   Closed  |     20    | 
 Project 3 |           |           | 
           |           |           |

If it is possible hope for help with syntax.

Upvotes: 0

Views: 118

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

This is a pain, because the result set is not in relational form: the columns in each row are not related to each other. But, you can do it, by using row_number() and either a full outer join or aggregation:

select p.project, s.status, e.employees
from (select project, row_number() over (order by (select null)) as seqnum
      from t
      group by project
     ) p full outer join
     (select status, row_number() over (order by (select null)) as seqnum
      from t
      group by status
     ) s
     on p.seqnum = s.seqnum full outer join
     (select employees, row_number() over (order by (select null)) as seqnum
      from t
      group by project
     ) e
     on e.seqnum = coalesce(p.seqnum, s.seqnum);

Upvotes: 1

Related Questions