Count the Number of Cities in the column

I have a column with Client Labels (separated by comma) associated with each Client.

I need to find Clients who has more or less than 1 State in the ClientLabels column. Another words, find exceptions where State either needs to be added (when no State specified at all) or removed (when extra State specified, usually happened when Client moved out of State or Employees Error).

All States we can serve: California, Arizona, Texas, Virginia and Washington.

P.S. If Client is moving to any State not in the above list, it becomes inactive right the way, so there is no way Alaska (for example) to be in the ClientLabels column.

CREATE TABLE Clients
(ClientId INT, ClientName VARCHAR(100), ClientLabels VARCHAR(max));

INSERT INTO Clients
VALUES
(1 , 'Justin Bieber', 'California, Musician, Male'),
(2 , 'Lionel Messi', 'Washington, Soccer Player, Male'),
(3 , 'Nicolas Cage', 'California, Actor, Male'),
(4 , 'Harry Potter', 'Fake, Male'),
(5 , 'Tom Holland', 'Arizona, Actor, California, Male'),
(6 , 'Ariana Grande', 'Texas, Musician, Female'),
(7 , 'Madonna', 'Virginia, Musician, Female'),
(8 , 'Dwayne Johnson', 'California, Actor, Male')


SELECT * FROM Clients

Output I need:

ClientId	ClientName	ClientLabels	NumberOfStates
1	Justin Bieber	California, Musician, Male	1
2	Lionel Messi	Washington, Soccer Player, Male	1
3	Nicolas Cage	California, Actor, Male	1
4	Harry Potter	Fake, Male	0
5	Tom Holland	Arizona, Actor, California, Male	2
6	Ariana Grande	Texas, Musician, Female	1
7	Madonna	Virginia, Musician, Female	1
8	Dwayne Johnson	California, Actor, Male	1

I've started the code, but don't know how to finish it:

SELECT c.*,
        COUNT(c.ClientLabels) OVER(PARTITION BY c.ClientId) AS NumberOfStates

FROM Clients AS c

CodePudding user response：

You may try the below query out.

declare @Clients table(ClientId INT, ClientName VARCHAR(100), ClientLabels VARCHAR(max))
declare @labels table(label varchar(100))

insert into @Clients
VALUES
(1 , 'Justin Bieber', 'California, Musician, Male'),
(2 , 'Lionel Messi', 'Washington, Soccer Player, Male'),
(3 , 'Tom Holland', 'Arizona, Actor, California, Male'),
(4 , 'Harry Potter', 'Fake, Male')

insert into @labels
values('California')
,('Arizona')
,('Texas')
,('Virginia')
,('Washington')

select distinct c.*,case when cl.label is null then 0 
else count(*)over(partition by clientid order by clientid) end as [NumberOfStates]  
from @Clients c
left join @labels cl
on c.ClientLabels like '%'   cl.label   '%'

CodePudding user response：

If you sql server version support STRING_SPLIT function you can try to use STRING_SPLIT with CROSS APPLY split ClientLabels by commna each ClientId.

Then use condition aggregate function count the NumberOfStates

SELECT ClientId,
       ClientName,
       ClientLabels,
       COUNT(CASE WHEN trim(v.value) IN ('California','Arizona', 'Texas', 'Virginia', 'Washington') THEN 1 END) NumberOfStates
FROM Clients c
CROSS APPLY STRING_SPLIT(c.ClientLabels,',') v
GROUP BY ClientId,ClientName,ClientLabels

sqlfiddle