pietlinden - Thursday, February 8, 2018 10:56 AM
I was wandering about that... In any case, it only requires a minor change to the code (see below). In your OP you mentioned DelimitedSplit8K so I won't go into details about it or where to get it.
There are only 3, very simple,changes that need to be made...
#1 use a single CSV parameter rather than separate parameters.
#2 in the 1st CTE, split the SSV parameter with the help of DelimitedSplit8K.
#3 in the WHERE clause, use "LEN(@_mv_skill_param) - LEN(REPLACE(@_mv_skill_param, ',', '')) + 1" to determine the number of elements in the CSV
... super easy...
USE tempdb;
GO
IF OBJECT_ID('tempdb.dbo.Skill', 'U') IS NULL
BEGIN -- DROP TABLE tempdb.dbo.Skill;
CREATE TABLE tempdb.dbo.Skill (
skill_id INT NOT NULL IDENTITY(1,1),
skill_name VARCHAR(30) NOT NULL
CONSTRAINT pk_Skill PRIMARY KEY CLUSTERED (skill_id),
CONSTRAINT uq_Skill_skillname UNIQUE (skill_name)
);
INSERT tempdb.dbo.Skill (skill_name) VALUES ('C#'), ('.net'), ('C++'), ('T-SQL'), ('PL SQL');
END;
IF OBJECT_ID('tempdb.dbo.Candidate', 'U') IS NULL
BEGIN -- DROP TABLE tempdb.dbo.Candidate;
CREATE TABLE tempdb.dbo.Candidate (
candidate_id INT NOT NULL IDENTITY(1,1),
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
CONSTRAINT pk_Candidate PRIMARY KEY CLUSTERED (candidate_id)
);
INSERT tempdb.dbo.Candidate (first_name, last_name) VALUES
('Bob', 'Jones'), ('Mary', 'Baxter'), ('Kevin', 'Hall'), ('John', 'Doe'),
('Alison', 'Chains'), ('Molly', 'Hatchet'), ('Maxwell', 'House'), ('Red', 'Fox');
END;
IF OBJECT_ID('tempdb.dbo.CandidateSkills', 'U') IS NULL
BEGIN -- DROP TABLE tempdb.dbo.CandidateSkills;
CREATE TABLE tempdb.dbo.CandidateSkills (
candidate_id INT NOT NULL,
skill_id INT NOT NULL,
proficiency_level INT NOT NULL
CONSTRAINT ck_CandidateSkills_proficiencylevel CHECK (proficiency_level BETWEEN 1 AND 5),
CONSTRAINT pk_CandidateSkills PRIMARY KEY CLUSTERED (candidate_id, skill_id)
);
INSERT tempdb.dbo.CandidateSkills (candidate_id, skill_id, proficiency_level) VALUES
(1, 1, 3), (1, 2, 2), (1, 5, 4), (2, 1, 4), (2, 3, 2), (2, 4, 1),
(3, 4, 5), (3, 1, 1), (4, 5, 5), (5, 1, 2), (5, 2, 2), (5, 3, 3),
(5, 4, 1), (5, 5, 2), (6, 2, 4), (6, 4, 4), (7, 1, 3), (7, 3, 3),
(6, 5, 2), (7, 5, 5), (8, 1, 4), (8, 4, 4);
END;
--SELECT * FROM dbo.Candidate c;
--SELECT * FROM dbo.Skill s;
--SELECT * FROM dbo.CandidateSkills cs;
--=======================================================================================
DECLARE
@_mv_skill_param VARCHAR(8000) = '1,3,4'; --<< using a single CSV parameter... compatible with SSRS MV parameter output.
WITH
cte_required_skills AS (
SELECT
skill_id = ds.Item
FROM
dbo.DelimitedSplit8K(@_mv_skill_param, ',') ds
),
cte_cs_eval AS (
SELECT
cs.candidate_id,
cs.skill_id,
cs.proficiency_level,
skill_count = COUNT(1) OVER (PARTITION BY cs.candidate_id),
total_proficiency = SUM(cs.proficiency_level) OVER (PARTITION BY cs.candidate_id)
FROM
cte_required_skills rs
JOIN dbo.CandidateSkills cs
ON rs.skill_id = cs.skill_id
)
SELECT
c.candidate_id,
c.first_name,
c.last_name,
s.skill_name,
ce.proficiency_level
FROM
cte_cs_eval ce
JOIN dbo.Candidate c
ON ce.candidate_id = c.candidate_id
JOIN dbo.Skill s
ON ce.skill_id = s.skill_id
WHERE
ce.skill_count = LEN(@_mv_skill_param) - LEN(REPLACE(@_mv_skill_param, ',', '')) + 1 --<< counts the delimiters and then adds 1
ORDER BY
ce.total_proficiency DESC;
HTH,
Jason