How to join the below table and getting the below output res

Question

Hello I Have one table- CourseMaster that contains two columns CourseId CourseName 1 Asp.Net 2 C# 3 SQL 4 VB And Now i am having one table OrganisationMaster contains two Columns OrgId Name CourseOffered 1 Mauli 1,4 2 SVS 3,4 3 Prompt 2,1 So i need query results as below Name CourseOffered Mauli Asp.Net, VB SVS SQL, VB Prompt C#, Asp.Net So How to write query to get the above result?

Piyush Pansuriya · Answer

Hello, Create function for CSV to Table : CREATE FUNCTION dbo.Fun_CSV_To_Table (@CSVString VARCHAR (8000), @Delimiter CHAR (1)) RETURNS @temptable TABLE (items VARCHAR (8000)) AS BEGIN DECLARE @pos INT ; DECLARE @slice VARCHAR (8000); SELECT @pos = 1; IF LEN(@CSVString) OR @CSVString IS NULL RETURN ; WHILE @pos!= 0 BEGIN SET @pos = CHARINDEX(@Delimiter,@CSVString); IF @pos != 0 SET @slice = LEFT (@CSVString, @pos - 1); ELSE SET @slice = @CSVString; IF( LEN(@slice) > 0) INSERT INTO @temptable(Items) VALUES (@slice); SET @CSVString = RIGHT (@CSVString, LEN(@CSVString) - @pos); IF LEN(@CSVString) = 0 BREAK; END RETURN END GO Now Create function for CSV_ID to CSV_Name : CREATE FUNCTION Fun_Get_CSV_Names (@Id_CSV NVARCHAR( MAX )) RETURNS nvarchar( MAX ) AS BEGIN DECLARE @Name_CSV Varchar ( MAX ); SELECT @Name_CSV = COALESCE (@Stage_Name_CSV+ ', ' , '' ) + dbo.CourseMaster.CourseName FROM dbo.CourseMaster ,dbo.Fun_CSV_To_Table(@Id_CSV, ',' ) AS TBL_DEPT WHERE CourseMaster.CourseId =TBL_DEPT.items return (@Name_CSV) END ; Now Simply Select statement : SELECT OrgId, Name ,Fun_Get_CSV_Names(CourseOffered) AS CourseOffered FROM OrganisationMaster

Shahriar Nikkhah · Answer

Piyush has the right answer. FYI If you are using SQL2017 or Azure SQL DB you can use STRING_AGG https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql