请教mssql如何实现这样的功能_Sql Server

请问mssql怎么实现这样的功能?
有这么一个表 News（NewsID，title，content,p_date，visitURL）

想让p_date的项相同的数据的visitURL字段的值从item-1开始自动填充为这种格式 item-1,item-2,item-3.......
比如：

1,title1,content1,2011-10-18,item-1
2,title1,content1,2011-10-18,item-2
3,title1,content1,2011-10-18,item-3
4,title1,content1,2011-10-18,item-4

5,title1,content1,2011-10-19,item-1
6,title1,content1,2011-10-19,item-2
7,title1,content1,2011-10-19,item-3
8,title1,content1,2011-10-19,item-4
9,title1,content1,2011-10-19,item-5
请问应该怎么实现

------解决方案--------------------

SQL code

select  NewsID,title,content,p_date,  visitURL='item-'+ltrim(row_number()over(partition by p_date order by getdate()))from  News
------解决方案--------------------
SQL codecreate table News(NewsID int,title varchar(8),content varchar(15),p_date date,visitURL varchar(6))insert into Newsselect 1,'title1','content1','2011-10-18','' union allselect 2,'title1','content1','2011-10-18','' union allselect 3,'title1','content1','2011-10-18','' union allselect 4,'title1','content1','2011-10-18','' union allselect 5,'title1','content1','2011-10-19','' union allselect 6,'title1','content1','2011-10-19','' union allselect 7,'title1','content1','2011-10-19','' union allselect 8,'title1','content1','2011-10-19','' union allselect 9,'title1','content1','2011-10-19',''  update a set a.visitURL='item-'+cast(b.s as varchar)from News ainner join(select NewsID,row_number() over(partition by p_date order by NewsID) s from News) bon a.NewsID=b.NewsIDselect * from NewsNewsID      title    content         p_date     visitURL----------- -------- --------------- ---------- --------1           title1   content1        2011-10-18 item-12           title1   content1        2011-10-18 item-23           title1   content1        2011-10-18 item-34           title1   content1        2011-10-18 item-45           title1   content1        2011-10-19 item-16           title1   content1        2011-10-19 item-27           title1   content1        2011-10-19 item-38           title1   content1        2011-10-19 item-49           title1   content1        2011-10-19 item-5