Hedefinize ulaşmanın birkaç yolu vardır:
SELECT Teams, Games = COUNT(*) FROM
(
SELECT
Teams = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END + ' vs ' +
CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END
FROM MY_TABLE
) AS T
GROUP BY Teams
VEYA, SQL 2005/2008 kullanıyorsanız
;WITH T AS (
SELECT
Teams = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END + ' vs ' +
CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END
FROM MY_TABLE
)
SELECT Teams, Games = COUNT(*) FROM T GROUP BY Teams
Yukarıdakilerin her ikisi de aynı sonucu verecektir.
/*
Teams Games
-------|------
A vs B 3
A vs C 1
B vs C 1
*/
İşte oynayabileceğiniz bir komut dosyası:
/* TEST DATA */
DECLARE @t AS TABLE ( ID INT, Team1 CHAR(1), Team2 CHAR(1), playdate [DATETIME] )
INSERT INTO @t (Team1 , Team2 , playdate)
SELECT 'A' , 'B', '20110525'
UNION ALL SELECT 'B' , 'A', '20110526'
UNION ALL SELECT 'A' , 'C', '20110527'
UNION ALL SELECT 'C' , 'B', '20110528'
UNION ALL SELECT 'A' , 'B', '20110528'
/* STYLE 1 */
;WITH T AS (
SELECT
Teams = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END + ' vs ' +
CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END
FROM @t
)
SELECT Teams, Games = COUNT(*) FROM T GROUP BY Teams
/* STYLE 2 */
SELECT Teams, Games = COUNT(*) FROM
(
SELECT
Teams = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END + ' vs ' +
CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END
FROM @t
) AS T
GROUP BY Teams
/ * VEYA, sadece sütunları değiştirmek için bunu kullanın * /
/* STYLE 3 */
;WITH T AS (
SELECT
Team1 = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END
, Team2 = CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END
FROM @t
)
SELECT Team1 , Team2, Games = COUNT(*) FROM T GROUP BY Team1 , Team2
/* STYLE 4 */
SELECT Team1 , Team2, Games = COUNT(*) FROM
(
SELECT
Team1 = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END
, Team2 = CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END
FROM @t
) AS T
GROUP BY Team1 , Team2