--Create normalized table to Append Data
create table ReportSubscription (
	CompanyID int not null,
	NewAllowedAccounts varchar(200) null)

--Need Tally Table
create table Tally (
    ID int not null)
   
go

set nocount on
declare @i int
select  @i = 0
while @i <50000
begin
    insert into Tally (ID)
    select (1 + @i)  
    select @i = @i + 1
end
go

--Insert Data to Normalized Table
INSERT INTO ReportSubscription SELECT CompanyID, 
NullIf(SubString(',' + allowedAccounts + ',' , ID , CharIndex(',' , ',' + allowedAccounts + ',' , ID) - ID) , '') AS Report 
FROM Tally, CompanySubscriptionReports 
WHERE ID <= Len(',' + allowedAccounts + ',') AND SubString(',' + allowedAccounts + ',' , ID - 1, 1) = ','

--Delete Duplicate Report ID from ReportSubscription
select distinct *
into holding
from ReportSubscription
truncate table ReportSubscription
insert ReportSubscription
select *
from holding
drop table holding

--Delete Reports From 00, 01, 02 and Delete Null
Delete ReportSubscription Where Left(Reports,2) in ('00','01','02')
Delete ReportSubscription where Reports is null
Delete ReportSubscription where Reports = ''

--Truncate CompanySubscriptionReports
truncate table CompanySubscriptionReports

--create a table to work with
create table workingtable (
    CompanyID int not null,
    NewAllowedAccounts varchar(200) not null,
    list varchar(7000),
    constraint pk_wt primary key (CompanyID,Reports) )

insert into workingtable (CompanyID ,Reports)
select CompanyID ,NewAllowedAccounts
from ReportSubscription
order by CompanyID ,NewAllowedAccounts

declare
    @list varchar(7000),
    @lasti int

select
    @list = '',
    @lasti = -1

--here is the meat of the work
update
    workingtable
set
    @list = list = case
                       when @lasti <> CompanyID  then NewAllowedAccounts
                       else @list + ', ' + NewAllowedAccounts
                   end,
    @lasti = CompanyID 

--Insert into CompanySubscription
INSERT INTO CompanySubscriptionReports ( CompanyID, allowedAccounts )
select
    CompanyID ,
    case
        when len(max(list)) > 7000 then convert(varchar(7000), left(max(list),6700))
        else convert(varchar(7000),max(list))
    end as list
from
    workingtable
group by
    CompanyID 
order by
    newid()
go

--Remove Unused tables
drop table workingtable
drop table ReportSubscription
drop table Tally