[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Parse through delimiters, calc combinations

Posted on 2013-11-25
9
Medium Priority
?
363 Views
Last Modified: 2013-11-26
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!
0
Comment
Question by:c0fee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
9 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39675371
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
0
 
LVL 2

Author Comment

by:c0fee
ID: 39677913
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!
0
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 1000 total points
ID: 39677949
Unfortunately the APPLY operator requires a compatibility level of 90 (SQL Server 2008) so it will not work for you in 2005.  There are many examples of split functions on the web that would work depending on how much flexibility you want.

http://www.biitb.com/index.php/t-sql-split-function/

An alternative to the APPLY operator would be to use a CURSOR and cycle through each entry in your table using the split function and inserting those rows in a temp table.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39678518
CROSS APPLY works in SQL 2005.
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39678522
Thanks, I was off...i forgot that 2008 and 2008R2 where the same compatibility level (100)
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 total points
ID: 39678537
For good performance, you want to use an inline table function, not a multi-statement table function, and one that's been tuned for performance.

You can create the function in a shared database and call it from any/all other dbs.


CREATE FUNCTION [dbo].[SplitStringIntoTable]  (
    @string varchar(8000),
    @delimiter varchar(5)
    )
RETURNS TABLE WITH SCHEMABINDING
AS
--SELECT ss.* FROM (SELECT 'ab/c/def/ghijklm/no/prq/////st/u//' AS string) AS test_values CROSS APPLY <db_name>.dbo.SplitStringIntoTable(test_values.string, '/') AS ss
RETURN
--Inline-CTE-Driven "tally table" produces values from 0 to 9999 ... enough to cover varchar(8000)
WITH cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [1000s].digit * 1000 + [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS num
    FROM cteDigits [1s] --0 thru 9
    CROSS JOIN cteDigits [10s] --thru 99
    CROSS JOIN cteDigits [100s] --thru 999
    CROSS JOIN cteDigits [1000s] --thru 9999
),
cteBase(num) AS ( --Limit the number of rows up front, for both a performance gain and prevention of accidental "overruns"
        SELECT 0 UNION ALL
        SELECT TOP (DATALENGTH(ISNULL(@string, 1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM cteTally
),
cteStart(num1) AS ( --This returns num + 1 (starting position of each "item" just once for each delimiter)
        SELECT b.num + 1
        FROM cteBase b
        WHERE (SUBSTRING(@string, b.num, 1) = @delimiter OR b.num = 0)
)
--Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ROW_NUMBER() OVER(ORDER BY s.num1) AS id,
       LTRIM(RTRIM(SUBSTRING(@string, s.num1, ISNULL(NULLIF(CHARINDEX(@delimiter, @string, s.num1), 0) - s.num1, 8000)))) AS value
FROM cteStart s;
GO
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39678555
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
0
 
LVL 2

Author Comment

by:c0fee
ID: 39678861
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.
0
 
LVL 2

Author Closing Comment

by:c0fee
ID: 39678865
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.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question