CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Ishaan Puniani
Ishaan Puniani

Reputation: 658

How to get records where csv column contains all the values of a filter

i have a table some departments tagged with user as 

User   | Department
user1  | IT,HR,House Keeping
user2  | HR,House Keeping
user3  | IT,Finance,HR,Maintainance
user4  | Finance,HR,House Keeping
user5  | IT,HR,Finance

i have created a SP that take parameter varchar(max) as filter (i dynamically merged if in C# code)

in the sp i creat a temp table for the selected filters eg; if user select IT & Finance & HR

i merged the string as IT##Finance##HR (in C#) & call the sp with this parameter

in SP i make a temp table as 

FilterValue
IT
Finance
HR

now the issue how can i get the records that contains all the departments taged with them (users that are associated with all the values in temp table) to get 

User   | Department
user3  | IT,Finance,HR,Maintainance
user5  | IT,HR,Finance

as optput

please suggest an optimised way to achive this filtering

Upvotes: 0

Views: 520

Answers (2)

Andriy M
Andriy M

Reputation: 77687

I agree with others that your design is, um, not ideal, and given the fact that it may change, as per your comment, one is not too motivated to find a really fascinating solution for the present situation.

Still, you can have one that at least works correctly (I think) and meets the situation. Here's what I've come up with:

;
WITH
  UserDepartment ([User], Department) AS (
    SELECT 'user1', 'IT,HR,House Keeping' UNION ALL
    SELECT 'user2', 'HR,House Keeping' UNION ALL
    SELECT 'user3', 'IT,Finance,HR,Maintainance' UNION ALL
    SELECT 'user4', 'Finance,HR,House Keeping' UNION ALL
    SELECT 'user5', 'IT,HR,Finance'
  ),
  Filter (FilterValue) AS (
    SELECT 'IT' UNION ALL
    SELECT 'Finance' UNION ALL
    SELECT 'HR'
  ),
  CSVSplit AS (
    SELECT
      ud.*,
      --x.node.value('.', 'varchar(max)')
      x.Value AS aDepartment
    FROM UserDepartment ud
    CROSS APPLY (SELECT * FROM dbo.Split(',', ud.Department)) x
  )
SELECT
  c.[User],
  c.Department
FROM CSVSplit c
  INNER JOIN Filter f ON c.aDepartment = f.FilterValue
GROUP BY
  c.[User],
  c.Department
HAVING
  COUNT(*) = (SELECT COUNT(*) FROM Filter)

The first two CTEs are just sample tables, the rest of the query is the solution proper.

The CSVSplit CTE uses a Split function that splits a comma-separated list into a set of items and returns them as a table. The entire CTE turns the row set of the form

-----  ---------------------------
user1  department1,department2,...
...    ...

into this:

-----  -----------
user1  department1
user1  department2
...    ...

The main SELECT joins the normalised row set with the filter table and selects rows where the number of matches exactly equals the number of items in the filter table. (Note: this implies there's no identical names in UserDepartment.Department).

Upvotes: 1

mwigdahl
mwigdahl

Reputation: 16578

This design is beyond horrible -- you should really change this to use truly relational design with a dependent table.

That said, if you are not in a position to change the design, you can limp around the problem with XML, and it might give you OK performance.

Try something like this (replace '@test' with your table name as needed...). You won't need to even create your temp table -- this will jimmy your comma-delimited string around into XML, which you can then use XQuery against directly:

DECLARE @test TABLE (usr int, department varchar(1000))

insert into @test (usr, department)
values (1, 'IT,HR,House Keeping')
insert into @test (usr, department)
values (2, 'HR,House Keeping')
insert into @test (usr, department)
values (3, 'IT,Finance,HR,Maintainance')
insert into @test (usr, department)
values (4, 'Finance,HR,House Keeping')
insert into @test (usr, department)
values (5, 'IT,HR,Finance')

;WITH departments (usr, department, depts)
AS
(
    SELECT usr, department, CAST(NULLIF('<department><dept>' + REPLACE(department, ',', '</dept><dept>') + '</dept></department>', '<department><dept></dept></department>') AS xml)
    FROM @test
)
SELECT departments.usr, departments.department
FROM departments
WHERE departments.depts.exist('/department/dept[text()[1] eq "IT"]') = 1
    AND departments.depts.exist('/department/dept[text()[1] eq "HR"]') = 1
    AND departments.depts.exist('/department/dept[text()[1] eq "Finance"]') = 1

Upvotes: 1

Related Questions