Getting child ids in string separate with delimiter along with parent id[SQL SERVER]

Hi Friends,

I come accros one requirement where I need... the child id should be delemited by '|' and along with the parent Id. Lets say I am having parent as company, one company is having more then one code.

So now my requirement is to get the company code in '|' saperated with company id, just like this.

Comp_id     company_code
----------- ------------
1           1|2
2           1|2|3|4
3           1|2

We can achive this by either using a user-defined aggregate function or using loop. I will explore the user-defined aggreagate function and post how to do, but for now lets do it with looping. Here is the code to achive this by using while-loop.

DECLARE @companies Table
(
    Comp_id INT,
    company_code  int
)
 
insert into @companies values(1,1)
insert into @companies values(1,2)
insert into @companies values(2,1)
insert into @companies values(2,2)
insert into @companies values(2,3)
insert into @companies values(2,4)
insert into @companies values(3,1)
insert into @companies values(3,2)
 
DECLARE @TmpTable Table
(
    Id int IDENTITY (0, 1) NOT NULL,
    CompanyId INT
)
 
SET NOCOUNT ON
 
DECLARE @Tmpcompanies Table
(
    Comp_id int,
    company_code  varchar(10)
)

INSERT INTO @TmpTable SELECT DISTINCT comp_id FROM @companies
 
DECLARE @MaxId INT
DECLARE @PipedList VARCHAR (MAX)
Declare @Count INT
Declare @CurrComp INT
 
SELECT @MaxId = MAX(id) FROM @TmpTable
SET @Count = 0
WHILE( @Count <= @MaxId)
BEGIN
    SET @PipedList = NULL
    SELECT @CurrComp = Comp_id, @PipedList = COALESCE(CAST(@PipedList AS VARCHAR(MAX)) + '|', '') 
    + CAST(company_code AS VARCHAR(10)) FROM @companies Cmp, @TmpTable TT
        WHERE CMP.Comp_id = TT.CompanyId AND TT.ID = @Count
    INSERT INTO @Tmpcompanies VALUES(@CurrComp, @PipedList)
    SET @Count = @Count + 1
END
 
SELECT * FROM @Tmpcompanies