This can be written in a more compact form, but just for clarity:
With Src As ( --< Source table Select * From (Values (1),(2),(3),(1),(1),(2),(3),(3),(4),(4),(5)) V (Id) ), Numbers As ( --< Auxiliary table with numbers from 1 to maximum row count of Src Select ROW_NUMBER() Over (Order By Id) As N From Src ), Counted As ( --< Calculate current number of ID occurances Select Id, Count(Id) As Cnt From Src Group By Id ) Select Id From Counted --< From distinct list of IDs Inner Join Numbers --< replicate each row On Numbers.N < Counted.Cnt --< one less time than the Cnt
Expression to replicate the row taken from SQL: Repeat a result row multiple times...
jpw implementation (please feel free to copy it into your own answer):
With Src As ( --< Source table Select * From (Values (1),(2),(3),(1),(1),(2),(3),(3),(4),(4),(5)) V (Id) ), Numbered As ( --< Number ID occurances Select Id, row_number() Over (Partition By id Order By id) As n From Src ) Select Id From Numbered Where n > 1 --< Take one off