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.

It now works for me as well, I must have made a mistake somewhere, apologies. — Stuart
– Stuart, Commented Sep 10, 2021 at 14:24
The table is not normalized. This is what makes working with it so hard. I suggest you change the database design (or have it changed), so it becomes a relational database as it is supposed to be, with a separate company_tag table. — Thorsten Kettner
– Thorsten Kettner, Commented Sep 10, 2021 at 14:24

John Cappelletti · Accepted Answer · 2021-09-10 14:02:16Z

10

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)

answered Sep 10, 2021 at 14:02

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Linoff Over a year ago

@JohnCappelletti . . . You and JSON. It's like dark magic.

Charlieface · Accepted Answer · 2021-09-10 15:31:29Z

3

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;

answered Sep 10, 2021 at 15:31

Charlieface

78.5k8 gold badges35 silver badges77 bronze badges

2 Comments

Aaron Bertrand Over a year ago

Totally just FYI but I've started to use ORDER BY @@SPID (and ORDER BY @@TRANCOUNT if you want to force serial execution) instead of ORDER BY (SELECT NULL). Some conversations about it with Paul White, Martin Smith, and Itzik BenGan here, here, and here.

Charlieface Over a year ago

Thanks, I did see that conversation when we were working on that series (I'm Charlie over there), but didn't see any benefit apart from shorter syntax. Looking over it again, the only benefit is that it allows a trivial plan, Martin points that out Also I couldn't bear it if Erik had to rename his website to orderbyspid.com, it just doesn't have the same ring :-)

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related