Database

khaliloenit wrote:

I must have a sql or batch script

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]

@ECHO OFF
FOR /F  %%i IN ('sqlcmd -S YourDBInstance -Q"set nocount on;select  CONVERT(DECIMAL(10,2),FVersion) from TVersion"') DO SET VNO=%%i

call .\v%VNO%\script.bat
@echo done.

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