How to split a columns as separate table in SQL Server

Question

I am trying to split a column in my table as separate table in SQL Server. I currently have a table with data. I have a table with available courses for a program separated by semi column. I need to split this and keep it as two different tables as I need to search based on a course details.

Current table (program_details)

program_code	course_available	start_date	active
1	AB;01;ERl;KL09;324	18-Sep-2022	1
2	ER;02;EJl;DL09;414	14-Sep-2022	1
3	JK;CD;201;PL08;201	28-Sep-2022	1
4	FV;50;301;GL07;234	18-Oct-2022	1

I need to split this as two table for better searchability with course codes, I can write program for this or is there any easy way to achieve this using any functions of SQL Server?

Table program_details:

program_code	start_date	active
1	18-Sep-2022	1
2	14-Sep-2022	1
3	28-Sep-2022	1
4	18-Oct-2022	1

Table program_course_mapping:

mapping_id	pgm_code	course_id
1	1	AB
2	1	01
3	1	ER1
4	1	KL09
5	1	324
6	2	ER
7	2	02
8	2	EJ1
9	2	DL09
10	2	414

score 0 · Answer 1 · answered Sep 20 '22 at 11:14

0

If you have SQL Server 2016,

SELECT program_code
      ,A.value FROM #T T
CROSS APPLY(SELECT * FROM STRING_SPLIT(T.Course_available,';')) A

answered Sep 20 '22 at 11:14

Srinivasan Rajasekaran

555
3
17

Why use a `SELECT` in the `APPLY` and not just go straight to the function? – Thom A Sep 20 '22 at 11:20
Since a table-valued function used SELECT @Larnu – Srinivasan Rajasekaran Sep 20 '22 at 11:25
But *why*? Why not `CROSS APPLY STRING_SPLIT(T.Course_available,';') A`? Why `APPLY` to a subquery? – Thom A Sep 20 '22 at 11:26
Was not sure about this way @Larnu. Will change it from next time. – Srinivasan Rajasekaran Sep 20 '22 at 11:29

How to split a columns as separate table in SQL Server

1 Answers1