Check the below article out. It gives a nice explanation along with SQL 2000 and 2005 syntax and query idea's.

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=DuplicateRows

Thanks,

S