Ciao, prova in questo modo
CREATE TABLE dbo.Test (
sc VARCHAR(10)
,st VARCHAR(10)
,psc INT
,pst INT
)
INSERT INTO dbo.Test
VALUES ('x','y',0,1)
,('x','z',2,1)
,('z','w',0,0)
,('w','x',2,3)
,('k','z',3,1)
SELECT s
,sum(pt) AS totpt
FROM (
SELECT sc AS s
,CASE
WHEN psc > pst
THEN 2
WHEN psc = pst
THEN 1
ELSE 0
END AS pt
FROM dbo.test
UNION ALL
SELECT st AS s
,CASE
WHEN psc < pst
THEN 2
WHEN psc = pst
THEN 1
ELSE 0
END AS pt
FROM dbo.test
) AS temptbl
GROUP BY s
ORDER BY totpt DESC
,s
L'ho buttata giù di getto, forse si può fare anche di meglio, ma dovrebbe comunque funzionare.