I have two tables
CREATE TABLE [Admin].[study_centre](
[study_centre_id] [int] IDENTITY(1,1) NOT NULL,
[study_centre_name] nvarchar NOT NULL,
[state_id] [int] NOT NULL,
[programme] nvarchar NULL,
[study_centre_status] [int] NOT NULL
)
I want to create a function from study_centre table that will display the fields study_centre_id, study_centre_name, state_id and programme. But it will split field programme and remove the ','. Then, create a stored procedure from the table programme(fields are programme_id, programme_code, programme_name) with a condition where programme_code = programme (call the function and equate the result of the split column) .
select * /* specify the fields you want to see; don't use "select *" */
from admin.study_centre as a
cross apply string_split(a.programme,',') as b
inner join programme as c
on c.programme_code=b.value
;
--sample data not to be applied in your case but to emulate your data
create table #study_centre(
[study_centre_id] [int] NOT NULL,
[study_centre_name] nvarchar(150) NOT NULL,
[state_id] [int] NOT NULL,
[programme] nvarchar(150) NULL,
[study_centre_status] [int] NOT NULL
)
insert into #study_centre
select 7, 'Ngwa High School Aba + Ovuku/Omoba Sec. Sch', 4, 'NCE, BDP, PGDE', 0 union
select 7, 'Girls Technical College Ab', 4, 'NCE, BDP', 0 union
select 7, 'GSSS KWOI', 5, 'NCE, BDP', 0 union
select 7, 'Oxon Hill High School', 5, null, 0
SELECT F1.[study_centre_id],
F1.[programme],
O.splitdata
FROM
(
SELECT *,
cast('<X>'+replace(isnull(F.[programme],''),',','</X><X>')+'</X>' as XML) as xmlfilter
from #study_centre F
)F1
CROSS APPLY
(
SELECT fdata.D.value('.','varchar(50)') as splitdata
FROM f1.xmlfilter.nodes('X') as fdata(D)) O
drop table #study_centre
