how to achieve table that each column data is distinct?

I guess my example is not clear. table is: :

 
id - name   -    work
-------------------------

1 - sina    -     programmer

2 - ali     -     programmer 
3 - jack    -     graphist    
4 - sina    -     graphist

i want to have below result that each column data is distinct.Suppose the columns in result table are indepen开发者_JS百科dent:

id - name   -    work
 
-------------------------
1  - sina   -     graphist

2  - ali    -     programmer

3  - jack   -     

tanks

You could do like this:

WITH names_CTE (Name, RowNum)
                AS
                (
                    SELECT DISTINCT Person.Name,
                    ROW_NUMBER() over (ORDER BY Name) RowNum
                    FROM Person
                )
                SELECT * FROM JBAccount_CTE cte

In this way each name will have a different number.

hi guys
this solution is my answer :

With CTE1 as  
(select *,ROW_NUMBER() over (partition by [name] order by id) as rn1 from @TAB )
,CTE2 as
(select *,ROW_NUMBER() over (partition by [work] order by id desc) as rn2 from @TAB )
select C1.Id,C1.[name],ISNULL(C2.[work],'') from CTE1 C1 left join CTE2 C2
on C1.rn1=1 and C2.rn2=1 and C1.[name]=C2.[name]
where C1.rn1=1
order by C1.id
---------OUPUT------------
--1 sina graphist
--2 ali programmer
--3 jack