Find popular combos
DECLARE @Sample TABLE
(
StudentID INT,
Class VARCHAR(20)
)
INSERT @Sample
SELECT 1, 'Maths' UNION ALL
SELECT 1, 'English' UNION ALL
SELECT 1, 'Science' UNION ALL
SELECT 2, 'Maths' UNION ALL
SELECT 2, 'English' UNION ALL
SELECT 2, 'Science' UNION ALL
SELECT 2, 'History' UNION ALL
SELECT 3, 'English' UNION ALL
SELECT 3, 'Maths' UNION ALL
SELECT 3, 'Science' UNION ALL
SELECT 3, 'RE' UNION ALL
SELECT 4, 'Science' UNION ALL
SELECT 4, 'Maths' UNION ALL
SELECT 4, 'English' UNION ALL
SELECT 4, 'History' UNION ALL
SELECT 4, 'French'
;WITH Yak(ClassName, ClassPath, Combinations)
AS (
SELECT Class,
CAST(Class AS VARCHAR(MAX)),
CAST(1 AS INT)
FROM @Sample
GROUP BY Class
UNION ALL
SELECT s.Class,
y.ClassPath + '-' + s.Class,
y.Combinations + 1
FROM Yak AS y
INNER JOIN @Sample AS s ON s.Class > y.ClassName
WHERE '-' + y.ClassPath + '-' NOT LIKE '%-' + s.Class + '-%'
)
SELECT y.ClassPath,
y.Combinations,
COUNT(f.Classes) AS Students
FROM (
SELECT ClassPath,
Combinations
FROM Yak
GROUP BY ClassPath,
Combinations
) AS y
LEFT JOIN (
SELECT STUFF(t.c, 1, 1, '') AS Classes
FROM (
SELECT StudentID
FROM @Sample
GROUP BY StudentID
) AS s
CROSS APPLY (
SELECT TOP 100 PERCENT
'-' + x.Class
FROM @Sample AS x
WHERE x.StudentID = s.StudentID
GROUP BY x.Class
ORDER BY x.Class
FOR XML PATH('')
) AS t(c)
) AS f ON f.Classes LIKE '%' + y.ClassPath + '%'
GROUP BY y.ClassPath,
y.Combinations
ORDER BY y.Combinations DESC,
COUNT(f.Classes) DESC,
y.ClassPath
OPTION (MAXRECURSION 0)
Legacy Comments
|
joe
2011-04-14 |
re: Find popular combos What does “combo” means? Short of “combination”? I cannot find the meaning of this in dictionary and a proper meaning in wiki. |
|
Peso
2011-04-21 |
re: Find popular combos It means Combinations, yes. |