Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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)

Print | posted on Tuesday, December 09, 2008 4:16 PM | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

Feedback

Gravatar

# 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.
4/14/2011 3:38 PM | joe
Gravatar

# re: Find popular combos

It means Combinations, yes.
4/21/2011 10:32 PM | Peso
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET