CODEWITHSUNDEEP
sqlsql-servermultiple-columnsdelimiter
Stuart
Stuart

Reputation: 69

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

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.

Upvotes: 3

Views: 11446

Answers (2)

Charlieface
Charlieface

Reputation: 71159

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;

Upvotes: 3

John Cappelletti
John Cappelletti

Reputation: 81930

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)

Upvotes: 10

Related Questions