{"id":829,"date":"2010-10-04T12:57:12","date_gmt":"2010-10-04T07:27:12","guid":{"rendered":"http:\/\/JitendraZaa.com\/blog\/?p=829"},"modified":"2010-10-04T12:57:12","modified_gmt":"2010-10-04T07:27:12","slug":"convert-rows-into-comma-separated-values-column-sql-server","status":"publish","type":"post","link":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/convert-rows-into-comma-separated-values-column-sql-server\/","title":{"rendered":"Convert rows into comma separated values column &#8211; SQL Server"},"content":{"rendered":"<p>Converting rows values into comma separated column value is required lots of time:<\/p>\n<p>example, i want to convert :<\/p>\n<pre>Name\n------------------------------\nIndia\nUSA\nJapan\nChina\nSwitzerland<\/pre>\n<p>to<\/p>\n<p><!--more--><\/p>\n<pre>Name\n------------------------------------\n India, USA, Japan, China, Switzerland<\/pre>\n<p>so, to achieve this, lets start with below example:<br \/>\nCreate a table datatype and insert values into this:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nDECLARE @Country TABLE (id INT, &#x5B;Name] Varchar(30) )\n\nINSERT INTO @Country VALUES (1,'India')\nINSERT INTO @Country VALUES (2,'USA')\nINSERT INTO @Country VALUES (3,'Japan')\nINSERT INTO @Country VALUES (4,'China')\nINSERT INTO @Country VALUES (5,'Switzerland')\n<\/pre>\n<p>I will first create xml and then with the help of <strong>stuff<\/strong> i will create comma separated value:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSELECT STUFF( -- Remove first comma\n(\n\tSELECT  ', ' + &#x5B;Name] FROM -- create comma separated values\n\t(\n\t  SELECT &#x5B;Name] FROM @Country --Your query here\n\t) AS T FOR XML PATH('')\n)\n,1,1,'') AS &#x5B;Name]\n<\/pre>\n<p>and output will be :<\/p>\n<p><span style=\"font-family: Consolas, Monaco, 'Courier New', Courier, monospace; line-height: 18px; font-size: 12px; white-space: pre;\">Name<\/span><\/p>\n<pre>------------------------------------\n India, USA, Japan, China, Switzerland<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Convert rows into comma separated values column &#8211; SQL Server<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"jz_research_post":"","_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[14],"tags":[333,292],"class_list":["post-829","post","type-post","status-publish","format-standard","hentry","category-sqlserver","tag-sql","tag-sql-server"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":1161,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/sql-server-pivot-table-example\/","url_meta":{"origin":829,"position":0},"title":"SQL Server Pivot Table Example","author":"Jitendra","date":"September 29, 2010","format":false,"excerpt":"Example and tutorial of using PIVOT in SQL Server","rel":"","context":"In &quot;SQL Server&quot;","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"SQL Server Pivot Table","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2010\/09\/SQL-Server-Pivot-Table.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":1422,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/update-from-statement-in-sql-server\/","url_meta":{"origin":829,"position":1},"title":"&#8220;Update From&#8221; Statement in SQL Server","author":"Jitendra","date":"December 17, 2010","format":false,"excerpt":"Example to demonstrate the \"Update From\" Statement in SQL Server. This example can be used in scenarios where developer wants to update the Table with the help of Inner Join.","rel":"","context":"In &quot;SQL&quot;","block_context":{"text":"SQL","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/"},"img":{"alt_text":"SQL Server Table 1","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2010\/12\/SQL-Server-Table-1.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":1006,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/case-statement-in-where-clause-sql-server-conditional-where-clause\/","url_meta":{"origin":829,"position":2},"title":"Case statement in Where Clause &#8211; SQL Server &#8211; Conditional Where clause","author":"Jitendra","date":"September 9, 2010","format":false,"excerpt":"Using Case statement in Where Clause in SQl Server. This script is used to create the conditional where clause","rel":"","context":"In &quot;SQL&quot;","block_context":{"text":"SQL","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/"},"img":{"alt_text":"Case statement in Where Clause","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2010\/09\/Case-statement-in-Where-Clause.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":1147,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/between-clause-problem-in-date-comparison-sql-server\/","url_meta":{"origin":829,"position":3},"title":"Between Clause problem in Date comparison &#8211; SQL Server","author":"Jitendra","date":"September 23, 2010","format":false,"excerpt":"SQL Server Date Comparison Tips : Never user Between clause with DateTime in SQL Server","rel":"","context":"In &quot;SQL Server&quot;","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":5882,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/sql-server-read-all-files-in-directory-and-store-in-table\/","url_meta":{"origin":829,"position":4},"title":"SQL Server &#8211; Read all files in directory and store in Table","author":"Jitendra","date":"January 18, 2017","format":false,"excerpt":"T-SQL Script to read all files in directory and store back in table - SQL Server","rel":"","context":"In &quot;SQL Server&quot;","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1125,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/get-table-information-column-name-data-type-in-sql-server\/","url_meta":{"origin":829,"position":5},"title":"Get Table information (Column Name, Data Type) in SQL Server","author":"Jitendra","date":"September 18, 2010","format":false,"excerpt":"Get Table information like Column Name, Data Type, Character length, Default Values etc in SQL Server","rel":"","context":"In &quot;SQL Server&quot;","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"SQL Server INFORMATION_SCHEMA.COLUMNS","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2010\/09\/SQL-Server-INFORMATION_SCHEMA.COLUMNS-300x44.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/829","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/comments?post=829"}],"version-history":[{"count":0,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/829\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/media?parent=829"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/categories?post=829"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/tags?post=829"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}