SQL为每个团队获胜，绘画，输掉表格桌

Question

我有一个sql query;这个例子切尔西球队获胜，输掉比赛结果但是我想要一行查询结果(每个团队(

for ex：

切尔西W，W，L，D，W

Arsenal W，D，D，L，L

mancity d，w，l，d，w

Select date, Team, (case when fthg > ftag then 'W' when fthg = ftag then 'D' when ftag > fthg then 'L' end) from ( select div, hometeam team, date, fthg, ftag from Matches union all select div, awayteam team, date, ftag, fthg from Matches ) a where Team like '%chelsea%' order by date desc, team desc date Team Mres 2016-08-15 00:00:00.000 Chelsea W 2016-08-27 00:00:00.000 Chelsea W 2016-09-16 00:00:00.000 Chelsea L 2016-10-15 00:00:00.000 Chelsea W 2016-10-23 00:00:00.000 Chelsea W 2016-11-05 00:00:00.000 Chelsea W 2016-11-26 00:00:00.000 Chelsea W 2016-12-11 00:00:00.000 Chelsea W 2016-12-26 00:00:00.000 Chelsea W 2016-12-31 00:00:00.000 Chelsea W 2017-01-22 00:00:00.000 Chelsea W 2017-02-04 00:00:00.000 Chelsea W 2017-02-25 00:00:00.000 Chelsea W 2017-04-01 00:00:00.000 Chelsea L 2017-04-05 00:00:00.000 Chelsea W

我如何为每个团队以行样式

导致行风格

有人可以帮我吗？thanx

Answer 1

怎么样。您会看到第一部分是基于您的代码的CTE(仅更改/添加列别名，并删除了顺序(，这允许脚本的其余部分像表格一样称呼它：

With TeamResults as
(
Select
  date as MatchDate, Team, 
  (case when fthg > ftag then 'W' when fthg = ftag then 'D' when ftag > fthg then 'L' end) Mres
      from 
      (
  select div, hometeam team, date, fthg, ftag from Matches
union all
  select div, awayteam team, date, ftag, fthg from Matches
  ) a
where Team like '%chelsea%'
)
SELECT t2.Team, TeamResultList = replace
                          ((SELECT Mres AS [data()]
                              FROM TeamResults t1
                              WHERE  t1.Team = t2.Team
                              ORDER BY t1.MatchDate FOR xml path('')), ' ', ',')
FROM TeamResults t2
GROUP BY t2.Team