Hi, I have created a table tblLOB with the fields Customer_ID, LOB (Line Of Busyness), Locations and filled it with your example data:
Using the following SQL instruction:
Code:
select * from tblLOB;
declare @LOB nvarchar(max)
declare @CustID int
create table #LOBs (CustID int, CustLOBs nvarchar(max))
declare cCustomers cursor fast_forward for
select distinct Customer_ID from tblLOB
OPEN cCustomers
FETCH NEXT FROM cCustomers INTO @CustID;
WHILE @@fetch_status = 0
BEGIN
select @LOB = coalesce (@LOB,'') + coalesce(LOB,'') + '-' --as LinesOfBussiness
from tblLOB where Customer_ID = @CustID
insert into #LOBs(custID, custLOBs) values (@CustID, @LOB)
select @LOB = ''
FETCH NEXT FROM cCustomers INTO @CustID
END
close cCustomers;
deallocate cCustomers;
select * from #LOBs;
drop table #LOBs
you get the following result:
CustID CustLOBs
1 PC-BH--
2 PT-CM--
3 BH-CM-
I don't know if this is the result you want, and it needs a bit of finetuning, but I hope it helps