CODEWITHSUNDEEP
sqlsql-server
TPC1980
TPC1980

Reputation: 3

SQL Grouping by Like using select statement

I have a table with unique names and a combination of those same names separated by commas in the same field as below:

Bill
Mark
Steve
Bill, Mark
Mark, Steve
Bill,Mark, Steve

I would like to Group the names not separated by a comma for a count of those where the name exists such as:

Bill 3
Mark 4
Steve 3

In the future someone may add another name to the table so I can't use a Case statement with static names. I would like something like this:

SELECT
   Name
FROM
   My_Table
Group By
   Name Like (SELECT Name FROM My_Table Where Name Not Like '%,%')

Is that possible?

Upvotes: 0

Views: 75

Answers (2)

Sam DeHaan
Sam DeHaan

Reputation: 10325

Select N.Name, COUNT(*) 
FROM (
  SELECT Name
  FROM My_Table
  WHERE Name NOT LIKE '%,%'
) Names N
JOIN My_Table MT
ON (MT.Name LIKE '%' + N.Name  + ',%' OR MT.Name LIKE '%,' + N.Name + '%' OR MT.Name = N.Name)
GROUP BY N.Name

Upvotes: 1

Larry Lustig
Larry Lustig

Reputation: 50980

I don't have an instance of SQLServer to test this out, but an approach that may work is selecting only the simple-name records and then a nested SELECT expression that will count all records with that name. Something like this:

 SELECT 
   Name, 
   (SELECT COUNT(*) FROM YourTable Y2 WHERE Name LIKE ('%' + Y1.Name + '%'))
   FROM YourTable Y1 WHERE NAME NOT LIKE '%,%'

It will fail, of course, on nested names. (Bob and Bobby, if they're different people, for instance). A more robust approach would require removing all the spaces from around commas and building out the LIKE expression into three LIKEs ORed together. If you can't create a LIKE value in-line the way I did, you can substitute whatever the SQLServer function for location-within-string is.

But, honestly, I'd project a temporary normalized table and base your report off that.

Upvotes: 0

Related Questions