Aug 13, 2008

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


DECLARE @Tmpcompanies Table
Comp_id int,
company_code varchar(10)

INSERT INTO @TmpTable SELECT DISTINCT comp_id FROM @companies

Declare @Count INT
Declare @CurrComp INT

SELECT @MaxId = MAX(id) FROM @TmpTable
SET @Count = 0
WHILE( @Count <= @MaxId)
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

SELECT * FROM @Tmpcompanies