SQL Majic!!

Sample Data:

CSE1102        Section-A        Jamal
CSE1102        Section-B        Kamal
CSE2107        Section-A       Tomal
CSE2107        Section-B        Rahim

Needed Output:

CSE1102           Jamal,Kamal
CSE2107          Tomal,Rahim

Code:

USE [dbRegistration]
GO
/****** Object: UserDefinedFunction [dbo].[fn_CombineTeacher] Script Date: 12/04/2011 10:36:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER function [dbo].[fn_CombineTeacher]
(
@StudentId int
)
--select[dbo].fn_CombineTeacher(201007007)
returns varchar(200)
as
begin
declare @name varchar(200)
set @name = ''
SELECT @name = @name + UserName + ', ' FROM
(SELECT DISTINCT dbo.T_UserSetup.UserName
FROM dbo.T_Term_Std_Result_Theory_Details LEFT OUTER JOIN
dbo.T_UserSetup RIGHT OUTER JOIN
dbo.T_Term_Teacher_Course ON dbo.T_UserSetup.UserId
= dbo.T_Term_Teacher_Course.TeacherId ON
dbo.T_Term_Std_Result_Theory_Details.CourseId
= dbo.T_Term_Teacher_Course.CourseId LEFT OUTER JOIN
dbo.T_Common_Department_Setup RIGHT OUTER JOIN
dbo.T_Term_Marks_Distribution INNER JOIN
dbo.T_Term_Setup ON dbo.T_Term_Marks_Distribution.TermId
= dbo.T_Term_Setup.TermId INNER JOIN
dbo.T_Common_Course_Information ON dbo.T_Term_Marks_Distribution.CourseType
= dbo.T_Common_Course_Information.CourseType ON
dbo.T_Common_Department_Setup.departmentId = dbo.T_Term_Setup.DepartmentId ON
dbo.T_Term_Std_Result_Theory_Details.TermId = dbo.T_Term_Setup.TermId AND
dbo.T_Term_Std_Result_Theory_Details.CourseId
= dbo.T_Common_Course_Information.CourseId LEFT OUTER JOIN
dbo.T_Std_General_Information ON dbo.T_Term_Std_Result_Theory_Details.StudentId
= dbo.T_Std_General_Information.StudentId
WHERE (dbo.T_Common_Course_Information.CourseType = 'Theory')
AND (dbo.T_Term_Setup.TermClosingStatus = 1) AND
dbo.T_Std_General_Information.StudentId = @StudentId )
AS s order by s.UserName
SET @name = LEFT(@name,LEN(@name)-1)
return @name
end

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s