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)

Answer 2

It's easier to pivot inside an APPLY, because then you can do it per row.

Pivoting using MAX(CASE is also usually more flexible.

Note that the ordering of the values are not guaranteed

SELECT
  s.CompanyName,
  s.CompanyNumber,
  v.*
FROM Source s
CROSS APPLY (
    SELECT
      MAX(CASE WHEN rn = 1 THEN Value END) AS Tag1,
      MAX(CASE WHEN rn = 2 THEN Value END) AS Tag2,
      MAX(CASE WHEN rn = 3 THEN Value END) AS Tag3      
    FROM (
        SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
        FROM STRING_SPLIT (s.Tags, ';') splitted
    ) v
) v;