"Distinct" column in SQL query

SELECT id, EmpNo FROM EmployeesTable

EmpNo can be the same for 1 or more records in the results of the above query. I now want to add another column derived from EmpNo(lets call it EmpNo2) but only returning distinct values of EmpNo.

For example if the above query returns 100 records 开发者_开发百科but there are 69 distinct EmpNo values and i modify the query to

SELECT id, EmpNo, Distinct EmpNo2
FROM  EmployeesTable EmpNo 

, i want all the 100 rows to be returned but the last column EmpNo2 should return 69 distinct values of EmpNo field.

But as already know, using distinct in that way results into an error but i want to implement such functionality - and a subquery is not helping.

SAMPLE REQUIRED RESULTS

   ID  EmpNo   EmpNo2

    1  0T4/HR 0T4/HR
    1  0T4/HR 2VP/E
    1  0T4/HR xT9/67
    1  0T4/HR 
    1  0T4/HR 
    2  2VP/E 
    2  2VP/E
    2  2VP/E 
    2  2VP/E 
    2  2VP/E 
    3  XT9/67 
    3  XT9/67 
    3  xT9/67 
    3  XT9/67 

How about:

Select id, empno, empno2
from employeestable left outer join (
 SELECT min([id]) as minid
      ,[empno] empno2
  FROM [EmployeesTable]
group by empno) etab2 on employeestable.id = etab2.minid

You're saying a subquery won't work, though - why not?

Your requirement is not clear and I also have very little information. Following is what you need. This can be even better but it is just a try.

declare @temp table
(
    uniqueid int identity(1, 1),
    id int,
    empno varchar(50),
    empno2 varchar(50)
)

insert into @temp  select   1,  '0T4/HR', null
insert into @temp select     1,  '0T4/HR' , null
insert into @temp select     1 , '0T4/HR' , null
insert into @temp select     1,  '0T4/HR' , null
insert into @temp select     1,  '0T4/HR'  , null
insert into @temp select     2,  '2VP/E'  , null
insert into @temp select     2,  '2VP/E' , null
insert into @temp select     2,  '2VP/E'  , null
insert into @temp select     2,  '2VP/E'  , null
insert into @temp select     2,  '2VP/E'  , null
insert into @temp select     3,  'XT9/67'  , null
insert into @temp select     3,  'XT9/67'  , null
insert into @temp select     3,  'xT9/67'  , null
insert into @temp select     3,  'XT9/67'  , null

SELECT ROW_NUMBER() OVER (ORDER BY id) AS id, empno into #temp FROM @temp group by empno, id

update @temp set empno2 = t2.empno
from @temp t inner join #temp t2 on t.uniqueid = t2.id

select * from @temp

drop table #temp