Avatar of Grayson Hampton
Grayson Hampton asked on

Help with query that Counts and TRIMS values in a column

I need to create a query that Counts the occurrences of BusinessSectorList to Name.
But, the values in BusinessSectorList have spaces and semi-colons.

The values in BusinessSectorList have the following 3 issues.

1. If there is a space (' Healthcare ') before or after the first value, remove it
2, If there is a space and semi-colon and another space ( ; Healthcare) before the first value, remove it.
3. If a semi-colon exist after the first value, remove it


Current Output Results:

BusinessSectorList                                                        Name
 Healthcare                                                                      Charlotte Spine Center
 ; Healthcare                                                                      Accolade, Inc.
Healthcare                                                                      Accolade, Inc.
Food;Wholesale                                                              The Schwan Food Company
Healthcare;Non-Profit                                                      12 and 12 Inc.



Desired Output Results:

BusinessSectorList                         Name                                                  Count
Healthcare                                       Charlotte Spine Center                     1
Healthcare                                       Accolade, Inc.                                     2
Healthcare                                        12 and 12 Inc.                                    1
Wholesale                                        The Schwan Food Company            1
* T-SQLSQL

Avatar of undefined
Last Comment
Ryan Chong

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Walter Ritzel

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Ryan Chong

what's the logic of this extraction?

Food;Wholesale                                                              The Schwan Food Company
Wholesale                                        The Schwan Food Company            1

Open in new window


Why you get the "Wholesale " here (which is the second node), while below:

Healthcare;Non-Profit                                                      12 and 12 Inc.
Healthcare                                        12 and 12 Inc.                                    1

Open in new window


you get the "Healthcare" (which is the first node)
awking00

Ryan Chong's question on why the semi-colon is treated differently in your example needs to be clarified in order to provide any solution.
ASKER
Grayson Hampton

What's the logic of this extraction?
In my "Desired Output Results" this was wrong
Wholesale                                        The Schwan Food Company            1

I meant to type
Food                                        The Schwan Food Company            1


I only want the first node.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Ryan Chong

you could try using XPath:

;with yourTable as
(
	Select ' Healthcare' BusinessSectorList, 'Charlotte Spine Center' Name union all
	Select ' ; Healthcare' BusinessSectorList, 'Accolade, Inc.' Name union all
	Select 'Healthcare' BusinessSectorList, 'Accolade, Inc.' Name union all
	Select 'Food;Wholesale ' BusinessSectorList, 'The Schwan Food Company' Name union all
	Select 'Healthcare;Non-Profit' BusinessSectorList, '12 and 12 Inc.' Name
), cte as
(
	Select *, cast( '<item>' + replace(trim(BusinessSectorList), ';','</item><item>') + '</item>' as xml) n
	from yourTable
), cte2 as
(
	Select a.*, trim(f.value('.[1]', 'nvarchar(50)')) BusinessSector
	from cte a
	CROSS APPLY a.n.nodes('//item[1]') t(f)
	Where trim(f.value('.[1]', 'nvarchar(50)')) <> ''
)
Select a.BusinessSector, a.Name, count(a.Name) Cnt
from cte2 a
Group By a.BusinessSector, a.Name

Open in new window


in case if you want to get the last node, use this instead:
a.n.nodes('//item[last()]')

Open in new window


XPath Syntax
https://www.w3schools.com/xml/xpath_syntax.asp