Solution to TSQL Beginners Challenge4

DECLARE @t TABLE(
	ID INT,
	NAME VARCHAR(MAX)
)

INSERT INTO @t (ID,[NAME]) SELECT 1, 'Deepak Kumar Goyal'
INSERT INTO @t (ID,[NAME]) SELECT 2, 'Niladri Biswas'
INSERT INTO @t (ID,[NAME]) SELECT 2, 'Priyanka Sarkar'
INSERT INTO @t (ID,[NAME]) SELECT 3, 'Sumi Girijan'
INSERT INTO @t (ID,[NAME]) SELECT 3, 'Suresh Beldar'
INSERT INTO @t (ID,[NAME]) SELECT 3, 'Jeeva Baby';

WITH X (ID, [NAME], List, CNT, LEV, RN)
AS (
SELECT T.ID,
		T.[NAME],
		CAST(T.[NAME] AS varchar(2000)) AS List,
		CAST(COUNT(T.ID) OVER(PARTITION BY T.ID) AS INT) AS CNT,
		1 AS LEV,
		T.RN
FROM 
	(
		SELECT ID,
			[NAME],
			ROW_NUMBER() OVER(PARTITION BY ID ORDER BY [NAME]) AS RN
		FROM @t
	) T

UNION ALL

SELECT TOP 100 PERCENT
		X.ID,
		X.[NAME],
		CASE WHEN CHARINDEX(e.[NAME],X.List)=0 THEN CAST(X.List + ';' + e.[NAME] AS varchar(2000))
		ELSE '' END,
		X.CNT,
		X.LEV+1,
		e.RN
FROM
	(
		SELECT ID,
			[NAME],
			ROW_NUMBER() OVER(PARTITION BY ID ORDER BY [NAME]) AS RN
		FROM @t
	) e,X
WHERE (e.ID=X.ID) AND e.RN>X.RN
)
SELECT 
	ID, 
	CASE WHEN (LEN(List)-LEN(REPLACE(List,';',''))=1) THEN REPLACE(List,';',' AND ') 
	WHEN (LEN(List)-LEN(REPLACE(List,';',''))>=2) THEN REPLACE(List,';',' OR ')
	ELSE List END AS [NAME]
FROM X
WHERE CNT = RN AND LEV=RN AND CNT=LEV AND (LEN(List)-LEN(REPLACE(List,';',''))+1=CNT) 
ORDER BY ID, LEN([List]) DESC;

Tags: