Get column values separated by semi colon

Learner1

I have two tables in plsql

tblStudent:-

StudentId            Name  .........
1                    A
2                    B

tblDept:-

DeptId              DeptName       StudentId
1                   Dep Aero         1
2                   IT               1
3                   Dep Maths        1
4                   Dep Chemistry    2

I want to get studentId, with all its departments which starts with 'Dep' separated by semi colon, If i pass where StudentId = 1 in SELECT result should look like

StudentId         DeptName
  1               Dep Aero;Dep Maths

any help please?

Alex Poole

If you want an empty list of departments when a student has no entries then you can use an outer join between the two tables, e.g.:

select s.studentid,
 listagg(d.deptname, ';') within group (order by d.deptname) as deptnames
from tblstudent s
left join tbldept d on d.studentid = s.studentid
and deptname  like 'Dep%'
group by s.studentid;

Demo using CTEs for your sample data, including a third student ID with no matching departments:

-- CTEs for sample data
with tblstudent (studentid, name) as (
            select 1, 'A' from dual
  union all select 2, 'B' from dual
  union all select 3, 'C' from dual
),
tbldept (deptid, deptname, studentid) as (
            select 1, 'Dep Aero', 1 from dual
  union all select 2, 'IT', 1 from dual
  union all select 3, 'Dep Maths', 1 from dual
  union all select 4, 'Dep Chemistry', 2 from dual
)
-- actual query
select s.studentid,
 listagg(d.deptname, ';') within group (order by d.deptname) as deptnames
from tblstudent s
left join tbldept d on d.studentid = s.studentid
and deptname  like 'Dep%'
group by s.studentid;

 STUDENTID DEPTNAMES                     
---------- ------------------------------
         1 Dep Aero;Dep Maths            
         2 Dep Chemistry                 
         3                               

Your data model looks odd though; you should probably have a department table which only has the department IDs and names, and then another tables that links each student to all of their departments - something like (in CTE form again):

-- CTEs for sample data
with tblstudent (studentid, name) as (
            select 1, 'A' from dual
  union all select 2, 'B' from dual
  union all select 3, 'C' from dual
),
tbldept (deptid, deptname) as (
            select 1, 'Dep Aero' from dual
  union all select 2, 'IT' from dual
  union all select 3, 'Dep Maths' from dual
  union all select 4, 'Dep Chemistry' from dual
),
tblstudentdept (studentid, deptid) as (
  select 1, 1 from dual
  union all select 1, 2 from dual
  union all select 1, 3 from dual
  union all select 2, 4 from dual
)
-- actual query
select s.studentid,
 listagg(d.deptname, ';') within group (order by d.deptname) as deptnames
from tblstudent s
left join tblstudentdept sd on sd.studentid = s.studentid
left join tbldept d on d.deptid = sd.deptid
and deptname  like 'Dep%'
group by s.studentid;

 STUDENTID DEPTNAMES                     
---------- ------------------------------
         1 Dep Aero;Dep Maths            
         2 Dep Chemistry                 
         3                               

Either way, if you only want to see a single student's results when add that as a where clause, right before the group by:

...
where s.studentid = 1
group by s.studentid;

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

How to semi_join two dataframes by string column with one being colon-separated

Excel - How to split semi-colon separated values in multiple columns into rows

How do I save an excel spreadsheet as a semi-colon separated values file?

How do I calculate semi-colon separated values within a cell?

How do I filter rows depending on values in semi-colon separated columns?

How to extract a specific column from : ( colon ) separated values?

Javascript: Get first number substring for each semi-colon separated substring

How to get Rows values as comma or semi colon sepeared values as output in sql

Node separate the lines in data separated by a semi-colon

how to take the specific details out in Python that are separated by a semi colon or a slash?

Get unique values in column separated by comma

Checking if a value is IN a colon separated column with in a table

PowerShell Filter from Array based on Email separated by semi-colon, Object value in the array separated by pipe

Get boxplot stats of a column separated by values in another column in dataframe in R

Get values from a column having comma separated values using Linq

Extracting values from a semi-colon delimited string

Get multiple column values from joined table in a comma separated list

Get distinct values from a column in a single comma separated string in sql?

Dealing with colon separated values when values themselves contain colons

Regex for one element or semi-colon separated list of max. 2 elements

Convert cURL multi-line output to single, semi-colon-separated line

Grouping the details of the data into one, price field should be separated by semi-colon

CustomActionData with semi-colon separated, causes string overflow - What are the common workaround to this solution?

How Can I Use Data Validation On Single or Multiple Email Addresses That are Semi-colon Separated?

Semi Colon Issue in JS

And Operator and Semi Colon

Regex Checks for semi colon (;)

How to convert string colon-separated column to MapType?

Filter rows with colon-separated text in a column based on a list