用SQL2000实现增加一列

[sunfor]

上图是左边是根据下面语句得出结果：
select username,datecol from [TestRows2Columns] group by username,datecol
现想增加一列如上图右边红色列，用SQL2000实现，增加一列是按username来定，有新的username就在新增列用COL从1开始赋值，后面相同名称的就+1，请教如何写？

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL
DROP TABLE t
GO
CREATE TABLE t(
	userName NVARCHAR(10),
	datecol DATETIME	
)
GO
SET NOCOUNT OFF
INSERT INTO t VALUES('李四','2020-01-02');
INSERT INTO t VALUES('李四','2020-01-06');
INSERT INTO t VALUES('王五','2020-01-03');
INSERT INTO t VALUES('王五','2020-01-04');
INSERT INTO t VALUES('王五','2020-01-05');
INSERT INTO t VALUES('张三','2020-01-01');
INSERT INTO t VALUES('张三','2020-01-07');
INSERT INTO t VALUES('张三','2020-01-08');
INSERT INTO t VALUES('张三','2020-01-09');

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp

SELECT t.userName,t.datecol
INTO #tmp
FROM t
GROUP BY t.userName,t.datecol
ORDER BY t.userName,t.datecol

SELECT *
,CAST((SELECT COUNT(1) FROM #tmp AS b WHERE a.userName=b.userName AND b.datecol<=a.datecol) AS VARCHAR(10))
 +a.userName AS 新增列
FROM #tmp AS a
/*
userName   datecol                 新增列
---------- ----------------------- --------------------
李四         2020-01-02 00:00:00.000 1李四
李四         2020-01-06 00:00:00.000 2李四
王五         2020-01-03 00:00:00.000 1王五
王五         2020-01-04 00:00:00.000 2王五
王五         2020-01-05 00:00:00.000 3王五
张三         2020-01-01 00:00:00.000 1张三
张三         2020-01-07 00:00:00.000 2张三
张三         2020-01-08 00:00:00.000 3张三
张三         2020-01-09 00:00:00.000 4张三
*/

[RINK_1]

如果同一个username的datecol没有重复，那就先把select username,datecol from [TestRows2Columns] group by username,datecol来插入一个临时表，然后再用下面试试。 select username,datecol,(select count(*) from #A where username=A.username and datecol<=A.datecol) as col_new from #A A