Loop, Sql Server - rextester

Language: Layout:

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

create table #agents (AgentID varchar(2), name varchar(50))
insert into #agents values
('A1','Julius Cesar'),
('B2','Albert Einstien')

create table #items (AgentID varchar(2), ItemID int, ItemName varchar(50))
insert into #items (AgentID, ItemID, ItemName) values
('A1',1,'Apple'),
('A1',2,'Pear'),
('A1',3,'Watermelon'),
('A1',4,'Grape'),
('B2',5,'Car'),
('B2',6,'Truck'),
('B2',7,'Van')

DECLARE c CURSOR FOR
SELECT DISTINCT
    AgentID
FROM #agents

DECLARE @AgentID VARCHAR(4)

--get the first agent id and place it into a variable
OPEN c
FETCH NEXT FROM c INTO @AgentID

--for each agent id, select some data where the agent id equals the current agent id in the cursor
WHILE @@FETCH_STATUS = 0
    BEGIN
         SELECT 
            AgentID, ItemID, ItemName
		 FROM #items 
         WHERE AgentID = @AgentID --or what ever is appropiate
		
		--get the next agent
		FETCH NEXT FROM c INTO @AgentID
	END

--clean up
CLOSE c
DEALLOCATE c

View schema
Execution time: 0,02 sec, rows selected: 7, rows affected: 9, absolute service time: 0,19 sec