SQL XML
-
I have this table:
MapID SeriesID Year
==== ====== ====
AAA 1 1971
AAA 1 1985
...
AAA 1 2000
BBB 1 1756
BBB 1 1757
...
AAA 2 1899
...
In other words, for every MapID and SeriesID there are several years. I need to stick all the years together to achieve the following:
MapID SeriesID AllYears
==== ====== =====
AAA 1 1971, 1985, ..., 2000
BBB 1 1756, 1757, ...
...
AAA 2 1899, ...
i.e. something like
SELECT MapID, SeriesID, MadeUpStringConcatenationFunction (Year) AS AllYears
FROM MyTableName
GROUP BY MapID, SeriesID
I understand the FOR XML PATH syntax in SQL can be used to stick string values in different rows together; however, so far I have only managed to combine everything into a single element, rather than one row per MapID/SeriesID combination (as shown above).
All answers gratefully received! (Or pointers to a different site if this is not the right forum.)
Cheers,
Ed Graham
-
This should do it
SELECT a.MapID,a.SeriesID,
STUFF((SELECT ','+CAST(b.Year AS VARCHAR(10)) AS "text()"
FROM MyTableName b
WHERE a.MapID=b.MapID
AND a.SeriesID=b.SeriesID
ORDER BY b.Year
FOR XML PATH('')),1,1,'') AS AllYears
FROM MyTableName a
GROUP BY a.SeriesID,a.MapID
ORDER BY a.SeriesID,a.MapID;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
That's excellent! Now working perfectly -- thank you very much indeed.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply