Here is the first part:
WITH x
AS (SELECT 'Math;Social;Science' unit_value FROM DUAL
UNION ALL
SELECT 'Math;Science;PE' FROM DUAL
UNION ALL
SELECT 'PE;Social;Math ' FROM DUAL
UNION ALL
SELECT 'Art;PE;Reading' FROM DUAL)
SELECT classes, COUNT (*)
FROM (SELECT SUBSTR (nums, 1, INSTR (nums, ',') - 1) classes
FROM (SELECT n,
SUBSTR (val,
1
+ INSTR (val,
',',
1,
n))
nums
FROM ( SELECT ROWNUM AS n, list.val
FROM ( SELECT REPLACE (
LTRIM (
SYS_CONNECT_BY_PATH
(unit_value,
','),
','),
';',
',')
val
FROM (SELECT ROW_NUMBER ()
OVER (PARTITION BY
1
ORDER BY 1)
rnum,
unit_value
FROM x)
WHERE CONNECT_BY_ISLEAF = 1 AND
ROWNUM = 1
CONNECT BY PRIOR rnum = rnum - 1) list
CONNECT BY LEVEL <
LENGTH (list.val)
- LENGTH (REPLACE (list.val, ',',
'')))))
GROUP BY classes;
Regards,
Mike
On Fri, Apr 8, 2011 at 9:44 AM, ORAQ <[email protected]> wrote:
> Hi
> Can some one please help me write an oracle query to get the # of
> students taking a particular class.
>
> I am using Oracel 9i
>
> I have a table with Student_id, classes, teacher
>
>
> and the values look like
>
>
> 111 Math;Social;Science Ms.Reed
> 112 Math;Science;PE Mr.Smith
> 113 PE;Social;Math Ms.Reed
> 114 Art;PE;Reading Mr.Jones
>
> (classes taken by students are stored in one field with ; separated
> values)
>
>
> I want the result to display as
> 1.Classname and studentcount
>
> Math 3
> Science 2
> Social 2
> Art 1
> PE 2
> Reading 1
>
>
> 2. Classname studentcount groupedby teacher name
>
> Math 2 Ms.Reed
> Math 1 Mr.Smith
> Social 2 Ms.Reed
> Science 1 Mr.Smith
> Science 1 Ms.Reed
> PE 1 Mr.Smith
>
> --
> You received this message because you are subscribed to the Google
> Groups "Oracle PL/SQL" group.
> To post to this group, send email to [email protected]
> To unsubscribe from this group, send email to
> [email protected]
> For more options, visit this group at
> http://groups.google.com/group/Oracle-PLSQL?hl=en
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
