asked on

Parse through delimiters, calc combinations

Hi,
Given this table where each class is delimited by a pipe:

STUDENT CLASSES
Jack Algebra 1|Spanish|Literature
Jill AP Calc| Spanish 2| History| Anatomy
James Study Hall
Jill Study Hall
Jill AP Calc

I need to find the total number of classes each student has and the total number of distinct permutations. It is NOT guaranteed that a row is distinct or that a class will not appear in another row for the same student.

Example Results:

Jill would have a total of 5 classes (AP Calc, Spanish 2, History, Anatomy, and Study Hall)

Total Student to Class Permutations: 9

Two separate SQL queries would be fine.

Thanks!

Brian Crowe

You will need a table-valued split function to break down the delimited class column then use CROSS APPLY. Could you define what you mean by "Total Student to Class Permutations"?

DECLARE       @t TABLE
(
      Student            VARCHAR(50),
      Classes            VARCHAR(250)
)

INSERT INTO @t (Student, Classes)
VALUES ('Jack', 'Algebra 1|Spanish|Literature'),
      ('Jill', 'AP Calc| Spanish 2| History| Anatomy'),
      ('James', 'Study Hall'),
      ('Jill', 'Study Hall'),
      ('Jill', 'AP Calc')

SELECT t.Student, COUNT(DISTINCT SplitClasses.Item)
FROM @t AS t
      CROSS APPLY dbo.Split(t.Classes, '|') AS SplitClasses
GROUP BY t.Student
ORDER BY t.Student

c0fee

ASKER

Hi BriCrowe - How do I create the function? I am using a SQL 2005 server (will also need to apply this 2008). I don't see it as an available function to call.

Also, by Total Student to Class Permutation - I meant that I need to know all possible combinations in the table. Jill has 5 combinations, Jack has 3, and James has 1 for a total of 9.

Thanks!

ASKER CERTIFIED SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

CROSS APPLY works in SQL 2005.

Brian Crowe

Thanks, I was off...i forgot that 2008 and 2008R2 where the same compatibility level (100)

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

SELECT t.Student, COUNT(DISTINCT ss.Value)
FROM @t AS t
CROSS APPLY <shared_db>.dbo.SplitStringIntoTable(t.Classes, '|') AS ss
GROUP BY t.Student
ORDER BY t.Student

c0fee

ASKER

Thank you both! I used BriCrowe's solution for what I needed - just a temporary analysis. But I appreciate having the other solution as well. I'm sure it will come in handy.

c0fee

ASKER

Thank you both! I used BriCrowe's solution for what I needed - just a temporary analysis. But I appreciate having the other solution as well. I'm sure it will come in handy.