SQL PUZZLE | The Presence of All Values Puzzle | SQL Interview Question

In this puzzle you have to split the list of dynamic values and match them with the vals column and if all the dynamic values matched with the vals for any Dat then show the data. If the dynamic values misses
any of the vals value then do not show any data. For more details please see the sample input and expected output.

Sample Input

Expected Output

OUTPUT FOR ‘A1,A2,A3’

OUTPUT FOR ‘A1’

Script – DDL and INSERT Sample Data

--

CREATE TABLE TestAll
(
	 Id INT
	,Dat VARCHAR(10)
	,Vals VARCHAR(10)
)
GO

INSERT INTO TestAll VALUES
(1,'A','A1'),
(2,'A','A2'),
(3,'A','A3'),
(4,'B','B1'),
(5,'B','B2')
GO

SELECT * FROM TestAll
GO

DECLARE @Vals AS VARCHAR(MAX) = 'A1,A2,A3'


--

SOLUTION – 1

--

DECLARE @Vals AS VARCHAR(MAX) = 'A1,A2,A3'
;WITH CTE AS
(
	SELECT value vals , COUNT(*) OVER() cnt FROM STRING_SPLIT(@Vals,',')
)
,CTE1 AS
(
	SELECT * , COUNT(*) OVER (PARTITION BY a.dat) cnt
	FROM TestAll a 
)
SELECT c1.Id,c1.Dat,c1.Vals FROM CTE1 c1 INNER JOIN CTE c 
ON c.cnt = c1.cnt AND c1.Vals = c.vals

--

Output – 1

--

Id          Dat        Vals
----------- ---------- ----------
1           A          A1
2           A          A2
3           A          A3

(3 rows affected)

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com