Sql query to add row id without loops

You'll want something like

SELECT ROW_NUMBER() OVER (PARTITION BY CompanyName ORDER BY Department) AS RN, * FROM YourTable

Note this will not give your actual results as there is nothing to order by that will give that output (remember a table has no intrinsic order, if you want results to display in a specific order you must specify it explicitly)