Skip to content

Instantly share code, notes, and snippets.

@alevyinroc

alevyinroc/String agg vs. stuff unordered.sql

Last active February 2, 2020 14:55
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save alevyinroc/b48ddc6b727654894aed2e00f1d3d8f1 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save alevyinroc/b48ddc6b727654894aed2e00f1d3d8f1 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
String agg vs. stuff unordered.sql
-- Original
SELECT DISTINCT location
,stuff((
SELECT ',' + cast(a2.npa AS CHAR(3))
FROM areacodes a2
WHERE a2.location = a.location
FOR XML PATH('')
), 1, 1, N'') AS areacodes
FROM areacodes a
ORDER BY location;
-- 2017 Edition
SELECT location
,string_agg(npa, ',') AS AreaCodes
FROM areacodes
GROUP BY location
ORDER BY location;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment