How to do Inner Join on Comma Separated Values
I have two tables. Now I want output as each student at single row with list of disorders as Comma Separated. How should I do this?
As you can see each student has with tbl_geneticdisorder.genetic_disorder_id as comma separated values.
I want each student to be displayed with comma separated disorder matching each comma separated tbl_geneticdisorder.genetic_disorder_id with tbl_geneticdisordermaster.ID
CREATE TABLE [dbo].[tbl_geneticdisorder](
[ID] [int] NOT NULL,
[udise_no] [varchar](20) NULL,
[student_regno] [int] NULL,
[genetic_disorder_id] [varchar](100) NULL
) ON [PRIMARY]
GO
/**************************************************/
INSERT [dbo].[tbl_geneticdisorder] ([ID], [udise_no], [student_regno], [genetic_disorder_id]) VALUES (13, N'1141701604', 1001, N'3,9,15,21,22,23')
GO
INSERT [dbo].[tbl_geneticdisorder] ([ID], [udise_no], [student_regno], [genetic_disorder_id]) VALUES (14, NULL, 1003, N'1,7,11')
GO
CREATE TABLE [dbo].[tbl_geneticdisordermaster](
[ID] [int] NOT NULL,
[disorder] [varchar](255) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[tbl_geneticdisordermaster] ([ID], [disorder]) VALUES (1, N'Albinism')
GO
INSERT [dbo].[tbl_geneticdisordermaster] ([ID], [disorder]) VALUES (2, N'Angelman syndrome')
GO
INSERT [dbo].[tbl_geneticdisordermaster] ([ID], [disorder]) VALUES (3, N'Ankylosing spondylitis')
GO