{"id":372,"date":"2019-08-03T05:07:12","date_gmt":"2019-08-02T23:37:12","guid":{"rendered":"https:\/\/techieshouts.com\/?p=372"},"modified":"2022-08-09T19:06:44","modified_gmt":"2022-08-09T13:36:44","slug":"ms-sql-server-query-to-return-a-comma-separated-string-from-a-table","status":"publish","type":"post","link":"https:\/\/techieshouts.com\/home\/ms-sql-server-query-to-return-a-comma-separated-string-from-a-table\/","title":{"rendered":"MS SQL Server query to return a comma-separated string from a table"},"content":{"rendered":"\n<p>In the other article, we saw the usage of Table variables. In this article, we will see how to transform column values in a table to a comma-separated string. <\/p>\n\n\n\n<p>Let&#8217;s create a table variable &#8220;Teams&#8221; with the columns &#8216;ID&#8217; and &#8216;Team&#8217; (use the below script).<\/p>\n\n\n\n<pre data-mode=\"sql\" data-theme=\"dreamweaver\" data-fontsize=\"14\" data-lines=\"Infinity\" class=\"wp-block-simple-code-block-ace\">DECLARE @Teams TABLE (ID INT IDENTITY(1,1), Team VARCHAR(50));\n\nINSERT INTO @Teams VALUES ('India'),('Australia'),('England'),('New Zealand'),('Pakistan'),('Sri Lanka'),('South Africa'),('Bangladesh');<\/pre>\n\n\n\n<p>When you fire a SELECT over the above table, it will return the result as shown below<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter is-resized\"><img loading=\"lazy\" src=\"https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/08\/image-1.png\" alt=\"\" class=\"wp-image-373\" width=\"162\" height=\"243\"\/><\/figure><\/div>\n\n\n\n<h3>Query to get comma-separated values<\/h3>\n\n\n\n<p>There are actually two ways to get comma separated values from the above table.<\/p>\n\n\n\n<h4>Solution 1<\/h4>\n\n\n\n<pre data-mode=\"sql\" data-theme=\"dreamweaver\" data-fontsize=\"14\" data-lines=\"Infinity\" class=\"wp-block-simple-code-block-ace\">DECLARE @A VARCHAR(1000) \r\nSELECT @A = COALESCE(@A + ',' + Team, Team) FROM @Teams \r\nSELECT @A as CommaSepStr<\/pre>\n\n\n\n<h4>Solution 2<\/h4>\n\n\n\n<pre data-mode=\"sql\" data-theme=\"dreamweaver\" data-fontsize=\"14\" data-lines=\"Infinity\" class=\"wp-block-simple-code-block-ace\">DECLARE @A VARCHAR(1000) \r\nSELECT @A = COALESCE(@A + ',', '') + Team FROM @Teams \r\nSELECT @A as CommaSepStr<\/pre>\n\n\n\n<p>Upon executing any of the above two queries, we will get the result as shown below.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img loading=\"lazy\" width=\"525\" height=\"67\" src=\"https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/08\/image-3.png\" alt=\"\" class=\"wp-image-377\" srcset=\"https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/08\/image-3.png 525w, https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/08\/image-3-300x38.png 300w\" sizes=\"(max-width: 525px) 100vw, 525px\" \/><\/figure><\/div>\n","protected":false},"excerpt":{"rendered":"<p>In the other article, we saw the usage of Table variables. In this article, we will see how to transform column values in a table to a comma-separated string. Let&#8217;s create a table variable &#8220;Teams&#8221; with the columns &#8216;ID&#8217; and &#8216;Team&#8217; (use the below script). When you fire a SELECT over the above table, it\u2026 <span class=\"read-more\"><a href=\"https:\/\/techieshouts.com\/home\/ms-sql-server-query-to-return-a-comma-separated-string-from-a-table\/\">Read More &raquo;<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[10,15],"tags":[54,39,40,41],"_links":{"self":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/372"}],"collection":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/comments?post=372"}],"version-history":[{"count":3,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/372\/revisions"}],"predecessor-version":[{"id":378,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/372\/revisions\/378"}],"wp:attachment":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/media?parent=372"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/categories?post=372"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/tags?post=372"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}