Split multiple values from a string in one column, into multiple columns using SQL Server

Question

I have a table in SQL server which outputs the following:

CompanyName	CompanyNumber	Tags
1st Comp Ltd	1	Credit broker;Limited Permission Lender;Insurance Intermediary
business.com	456	Investment Advisor;Credit Broking Only
Charity.org	156789	Not for profit

I want to split the values in the tag column so that there is only one tag per column, so:

CompanyName	CompanyNumber	Tag1	Tag2	Tag3
1st Comp Ltd	1	Credit broker	Limited Permission Lender	Insurance Intermediary
business.com	456	Investment Advisor	Credit Broking Only
Charity.org	156789	Not for profit

I can do this manually in excel using the semicolon as a delimiter, and then adjust the headers, but can this be done in SQL server? Ultimately I would like a view in SQL server to format the data so I can have a powershell script generate a csv and send in an email.

I've tried the following, I think I might be nearly there, it just doesn't work in a view:

with TagsDelimited_CTE AS
(select CompanyName, CompanyNumber, Value,
ROW_NUMBER() over(partition by CompanyName, CompanyNumber order by CompanyName, CompanyNumber) as RowNum
from  Source
CROSS APPLY
string_split(Tags,';') 
)
select CompanyName, CompanyNumber,
[1] as Tag1,
[2] as Tag2,
[3] as Tag3
From TagsDelimited_CTE
PIVOT
(MAX(value)
For RowNum in ([1],[2],[3])) as PVT

Any assistance would be a big help, thanks.

Answer 1

With a bit of JSON and assuming you have a known or maximum number of tags

Select A.CompanyName
      ,A.CompanyNumber
      ,Tag1  = JSON_VALUE(S,'$[0]')
      ,Tag2  = JSON_VALUE(S,'$[1]')
      ,Tag3  = JSON_VALUE(S,'$[2]')
From  YourTable A
Cross Apply ( values ( '["'+replace(STRING_ESCAPE(Tags,'json'),';','","')+'"]' ) ) B(S)