按照指定格式显示数据表数据

表结构和数据如下所示：

 id          dName
----------- -----
1           a
4           a
2           b
3           b
5           c
6           c

要求使用一条查询语句查询出如下效果：

编号      列名  出现次数
------    ----  ---------
1           a       2
4           a       2
2           b       2
3           b       2
5           c       2
6           c       2

苦思许久，没结果，就写了个大致游标实现的方法，完善后如下

create function fun_show()
returns @tab table
(
 列a int,
 列b char(1),
 列c int
)
as
begin
 declare myCursor cursor for select dName from DBTable group by dName
 open myCursor
 declare @name char(1)
 fetch next from myCursor into @name
 while(@@fetch_status=0)
 begin
  declare @count int
  --求的数量
  select @count=count(*) from DBTable where dName=@name
  declare myCur cursor for select id from DBTable where dName=@name
  open myCur
  declare @id int
  fetch next from myCur into @id
  while(@@fetch_status=0)
  begin
   insert into @tab values(@id,@name,@count)
   fetch next from myCur into @id
  end
  close myCur
  deallocate myCur
  fetch next from myCursor into @name
 end
 close myCursor
 deallocate myCursor
 return
end

烦请高人帮忙完善查询实现。谢谢。