Database

Unpivot(Course for Courses in (English, Physics, Mathematics, Engineering))as upv

Pivot(MAX(course) for Student in (Jane, Michelle, Dan)) as pv

ID	Courses		Jane	Michelle Dan
1	Engineering	A	NULL	NULL
2	Engineering	NULL	A	NULL
3	Engineering	NULL	NULL	B
1	English		A	NULL	NULL
2	English		NULL	A	NULL
3	English		NULL	NULL	A
1	Mathematics	A	NULL	NULL
2	Mathematics	NULL	B	NULL
3	Mathematics	NULL	NULL	A
1	Physics		B	NULL	NULL
2	Physics		NULL	A	NULL
3	Physics		NULL	NULL	A

select Courses, MAX(ISNULL(Jane,'')) as Jane, MAX(ISNULL(Michelle,'')) as Michelle, MAX(ISNULL(Dan,'')) as Dan
from 
	(Select * from TestScores
	Unpivot(Course for Courses in (English, Physics, Mathematics, Engineering))
	as upv ) UPV
Pivot(MAX(Course) for Student in (Jane, Michelle, Dan)) as pv
GROUP BY Courses

DECLARE @courseNames table (id int identity(1,1), course nvarchar(25))
insert into @courseNames values ('English'),('Physics'),('Mathematics'),('Engineering')

select cn.id, Courses, MAX(ISNULL(Jane,'')) as Jane, MAX(ISNULL(Michelle,'')) as Michelle, MAX(ISNULL(Dan,'')) as Dan
from 
	(Select * from TestScores
	Unpivot(Course for Courses in (English, Physics, Mathematics, Engineering))
	as upv ) UPV
Pivot(MAX(Course) for Student in (Jane, Michelle, Dan)) as pv
inner join @courseNames cn ON pv.Courses=cn.course
GROUP BY cn.id, Courses
ORDER BY cn.id

;with q as
(
	select Courses, MAX(ISNULL(Jane,'')) as Jane, MAX(ISNULL(Michelle,'')) as Michelle, MAX(ISNULL(Dan,'')) as Dan
	from 
		(Select * from TestScores
		Unpivot(Course for Courses in (English, Physics, Mathematics, Engineering))
		as upv ) UPV
	Pivot(MAX(Course) for Student in (Jane, Michelle, Dan)) as pv
	GROUP BY Courses
)
select q.Courses, q.Jane, q.Michelle, q.Dan
FROM q
inner join @courseNames cn ON q.Courses=cn.course
ORDER BY cn.id

SET param_payroll_exists = (SELECT COUNT(*) FROM payroll WHERE location_id = param_location AND payroll_month = param_month AND payroll_year = param_year);

IF EXISTS (param_payroll_exists > 0) THEN
    SET param_payroll_id = 0;
END IF;

Jassim Rahma wrote:

IF EXISTS (param_payroll_exists > 0) THEN

CREATE TRIGGER tri_add_clr
ON dbo.foriegn
FOR INSERT
AS
EXTERNAL NAME triggertest.[YourNamespace.Class1].abc

srikrishnathanthri wrote:

CREATE ASSEMBLY triggertest
FROM 'D:\dll\Interop.ADODB.dll'
WITH PERMISSION_SET = SAFE

Richard Deeming wrote:

CREATE TRIGGER tri_add_clr
ON dbo.foriegn
FOR INSERT
AS
EXTERNAL NAME triggertest.[YourNamespace.Class1].abc

Msg 6573, Level 16, State 1, Procedure tri_add_clr, Line 1
Method, property or field 'abc' of class 'ClassLibrary1.Class1' in assembly 'triggerdll' is not static.

Public Class Class1
    Public Shared Function abc(ByVal a As String) As String
        Return a.ToUpper
    End Function
End Class

SELECT
    ...
FROM
    t1
    INNER JOIN t2
    ON t2.CampusID = t1.CampusID
    INNER JOIN t3
    ON t3.StaffID = t1.StaffID
ORDER BY
    ...
;