By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable
(location varchar(16),
team varchar(3),
score integer);
INSERT INTO mytable
(location,
team,
score)
VALUES
('Adelong', 'SFP', 104),
('Adelong', 'LIB', 189),
('Adelong', 'CDP', 9),
('Hurstville', 'SFP', 14),
('Hurstville', 'LIB', 64),
('Hurstville', 'CDP', 13);
6 rows affected
SELECT t1.location [@name],
(SELECT t2.team,
t2.score
FROM mytable t2
WHERE t2.location = t1.location
FOR XML PATH('node'),
TYPE)
FROM (SELECT DISTINCT
t1.location
FROM mytable t1) t1
FOR XML PATH('location');
| XML_F52E2B61-18A1-11d1-B105-00805F49916B |
|---|
| <location name="Adelong"><node><team>SFP</team><score>104</score></node><node><team>LIB</team><score>189</score></node><node><team>CDP</team><score>9</score></node></location><location name="Hurstville"><node><team>SFP</team><score>14</score></node><node><team>LIB</team><score>64</score></node><node><team>CDP</team><score>13</score></node></location> |