We help IT Professionals succeed at work.

Help with query that Counts and TRIMS values in a column

Grayson Hampton
on
32 Views
Last Modified: 2020-04-14
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
Comment
Watch Question

Senior Software Engineer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
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)
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
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.
Grayson HamptonBI Director

Author

Commented:
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.
CERTIFIED EXPERT

Commented:
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
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.