Database

Jörgen Andersson wrote:

without using procedural code

Mycroft Holmes wrote:

can't see the PK field

Jörgen Andersson wrote:

m going to try to do it using a recursive CTE.

1. Cross-join the two tables;
2. Generate the ranking value for each pair;
3. Generate a ROW_NUMBER, partitioned by the primary key of Y, and ordered by the ranking value (descending);
4. Select the rows where the row number is 1;

WITH cteRanked As
(
    SELECT
        X.PK As XPK,
        X.OtherColumnsFromX,
        Y.PK As YPK,
        Y.OtherColumnsFromY,
        RankingFunction(X.Value, Y.Value) As R
    FROM
        X CROSS JOIN Y
),
cteOrdered As
(
    SELECT
        XPK,
        OtherColumnsFromX,
        YPK,
        OtherColumnsFromY,
        ROW_NUMBER() OVER (PARTITION BY YPK ORDER BY R DESC) As RN
    FROM
        cteRanked
)
SELECT
    XPK,
    OtherColumnsFromX,
    YPK,
    OtherColumnsFromY
FROM
    cteOrdered
WHERE
    RN = 1
;

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