SQL getting win , draw , lose form table for each team

Question

I have an sql query ; This example chelsea team win, lose, draw match results But i want query results (for each team) in a rows

For ex:

Chelsea 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

How do i this results in row style for each team

Can somebody help me ? Thanx

What is the desired output? I am guessing you want to pivot the data dynamically... which is better done in application code. — Gurwinder Singh, Apr 23 '17 at 06:28
In your desired output, are the Ws, Ls and Ds all in one column but separated by commas, or all in separate columns? Also in the output from your current code, you have Ms and Gs rather than W, L, D. Just a typo (possibly because you're translating)? — Steve Lovell, Apr 23 '17 at 14:03
If you're happy with one column with Ws, Ls and Ds but separated by commas: http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 — Steve Lovell, Apr 23 '17 at 14:04
@SteveLovell Do you have any suggestions for other question http://stackoverflow.com/questions/43594482/sql-soccer-points-table-last-5-matches — Robin Vlaar, Apr 25 '17 at 06:25

Steve Lovell · Accepted Answer · 2017-04-23T16:38:55.303

0

How about this. You'll see the first part is a CTE based on your code (only column aliases changed/added and the order by removed), which allows the remainder of the script to refer to it like a table:

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

edited Apr 23 '17 at 16:38

answered Apr 23 '17 at 15:56

Steve Lovell

2,564
2
13
16

Receive an error ` Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'Select'. Msg 102, Level 15, State 1, Line 14 Incorrect syntax near ')'. ` I try improved and made some changes but it didn't work... – Robin Vlaar Apr 23 '17 at 16:11
I missed a key word. "AS" right near the start! Added above. – Steve Lovell Apr 23 '17 at 16:20
Also, I shouldn't have had the `ORDER BY` in the first part. That's now removed above. I'd only tested the second half, as I didn't have data for the `Matches` table. – Steve Lovell Apr 23 '17 at 16:29
Thank you so much.. But I had to remove " order by date desc, team desc " line or it didn't work...? – Robin Vlaar Apr 23 '17 at 16:34

SQL getting win , draw , lose form table for each team

1 Answers1