Unique results for each column in 1 table in SQL Server

sql sql-server unique distinct sql-server-2014

80 просмотра

1 ответ

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.

Автор: Dmitry Источник Размещён: 08.11.2019 11:05

Ответы (1)


1 плюс

Решение

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);
Автор: Gordon Linoff Размещён: 20.08.2016 11:16
Вопросы из категории :
32x32