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

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?

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) < 1 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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.