{"id":19098,"date":"2024-03-15T18:13:58","date_gmt":"2024-03-15T18:13:58","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/how-to-merge-multiple-rows-in-sql-server-into-one-row\/"},"modified":"2024-03-21T14:53:20","modified_gmt":"2024-03-21T14:53:20","slug":"how-to-merge-multiple-rows-in-sql-server-into-one-row","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/how-to-merge-multiple-rows-in-sql-server-into-one-row\/","title":{"rendered":"How to merge multiple rows in SQL Server into one row?"},"content":{"rendered":"<p>You can merge multiple lines into one by using the following method:<\/p>\n<ol>\n<li>To generate XML without using a specific tag name.<\/li>\n<\/ol>\n<pre class=\"post-pre\"><code><span class=\"hljs-keyword\">SELECT<\/span> STUFF((<span class=\"hljs-keyword\">SELECT<\/span> <span class=\"hljs-string\">','<\/span> <span class=\"hljs-operator\">+<\/span> ColumnName\r\n              <span class=\"hljs-keyword\">FROM<\/span> TableName\r\n              <span class=\"hljs-keyword\">FOR<\/span> XML PATH(<span class=\"hljs-string\">''<\/span>)), <span class=\"hljs-number\">1<\/span>, <span class=\"hljs-number\">1<\/span>, <span class=\"hljs-string\">''<\/span>) <span class=\"hljs-keyword\">AS<\/span> MergedColumn\r\n<span class=\"hljs-keyword\">FROM<\/span> TableName\r\n<\/code><\/pre>\n<ol>\n<li>combine a string into one aggregated string<\/li>\n<\/ol>\n<pre class=\"post-pre\"><code><span class=\"hljs-keyword\">SELECT<\/span> STRING_AGG(ColumnName, <span class=\"hljs-string\">','<\/span>) <span class=\"hljs-keyword\">AS<\/span> MergedColumn\r\n<span class=\"hljs-keyword\">FROM<\/span> TableName\r\n<\/code><\/pre>\n<ol>\n<li>Utilize recursive CTE (common table expressions) technique:<\/li>\n<\/ol>\n<pre class=\"post-pre\"><code><span class=\"hljs-keyword\">WITH<\/span> RecursiveCTE <span class=\"hljs-keyword\">AS<\/span> (\r\n    <span class=\"hljs-keyword\">SELECT<\/span> ColumnName, <span class=\"hljs-built_in\">ROW_NUMBER<\/span>() <span class=\"hljs-keyword\">OVER<\/span> (<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> ColumnName) <span class=\"hljs-keyword\">AS<\/span> RowNumber\r\n    <span class=\"hljs-keyword\">FROM<\/span> TableName\r\n    <span class=\"hljs-keyword\">UNION<\/span> <span class=\"hljs-keyword\">ALL<\/span>\r\n    <span class=\"hljs-keyword\">SELECT<\/span> r.ColumnName <span class=\"hljs-operator\">+<\/span> <span class=\"hljs-string\">','<\/span> <span class=\"hljs-operator\">+<\/span> t.ColumnName, t.RowNumber\r\n    <span class=\"hljs-keyword\">FROM<\/span> RecursiveCTE r\r\n    <span class=\"hljs-keyword\">INNER<\/span> <span class=\"hljs-keyword\">JOIN<\/span> TableName t <span class=\"hljs-keyword\">ON<\/span> t.RowNumber <span class=\"hljs-operator\">=<\/span> r.RowNumber <span class=\"hljs-operator\">+<\/span> <span class=\"hljs-number\">1<\/span>\r\n)\r\n<span class=\"hljs-keyword\">SELECT<\/span> TOP <span class=\"hljs-number\">1<\/span> ColumnName\r\n<span class=\"hljs-keyword\">FROM<\/span> RecursiveCTE\r\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> RowNumber <span class=\"hljs-keyword\">DESC<\/span>\r\nOPTION (MAXRECURSION <span class=\"hljs-number\">0<\/span>)\r\n<\/code><\/pre>\n<p>Please note that the above method is applicable for SQL Server 2017 or higher versions. In earlier versions, you may need to use different methods to achieve the merging of multiple rows into one.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>You can merge multiple lines into one by using the following method: To generate XML without using a specific tag name. SELECT STUFF((SELECT &#8216;,&#8217; + ColumnName FROM TableName FOR XML PATH(&#8221;)), 1, 1, &#8221;) AS MergedColumn FROM TableName combine a string into one aggregated string SELECT STRING_AGG(ColumnName, &#8216;,&#8217;) AS MergedColumn FROM TableName Utilize recursive CTE [&hellip;]<\/p>\n","protected":false},"author":8,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","footnotes":""},"categories":[1],"tags":[],"class_list":["post-19098","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.5 (Yoast SEO v21.5) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How to merge multiple rows in SQL Server into one row? - Blog - Silicon Cloud<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.silicloud.com\/blog\/how-to-merge-multiple-rows-in-sql-server-into-one-row\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to merge multiple rows in SQL Server into one row?\" \/>\n<meta property=\"og:description\" content=\"You can merge multiple lines into one by using the following method: To generate XML without using a specific tag name. SELECT STUFF((SELECT &#039;,&#039; + ColumnName FROM TableName FOR XML PATH(&#039;&#039;)), 1, 1, &#039;&#039;) AS MergedColumn FROM TableName combine a string into one aggregated string SELECT STRING_AGG(ColumnName, &#039;,&#039;) AS MergedColumn FROM TableName Utilize recursive CTE [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/blog\/how-to-merge-multiple-rows-in-sql-server-into-one-row\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog - Silicon Cloud\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\" \/>\n<meta property=\"article:published_time\" content=\"2024-03-15T18:13:58+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-21T14:53:20+00:00\" \/>\n<meta name=\"author\" content=\"William Carter\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:site\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"William Carter\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-merge-multiple-rows-in-sql-server-into-one-row\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-merge-multiple-rows-in-sql-server-into-one-row\/\"},\"author\":{\"name\":\"William Carter\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/f697031891aacefc4b681d139781d3c0\"},\"headline\":\"How to merge multiple rows in SQL Server into one row?\",\"datePublished\":\"2024-03-15T18:13:58+00:00\",\"dateModified\":\"2024-03-21T14:53:20+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-merge-multiple-rows-in-sql-server-into-one-row\/\"},\"wordCount\":79,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-merge-multiple-rows-in-sql-server-into-one-row\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/how-to-merge-multiple-rows-in-sql-server-into-one-row\/\",\"name\":\"How to merge multiple rows in SQL Server into one row? - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-15T18:13:58+00:00\",\"dateModified\":\"2024-03-21T14:53:20+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-merge-multiple-rows-in-sql-server-into-one-row\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/how-to-merge-multiple-rows-in-sql-server-into-one-row\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-merge-multiple-rows-in-sql-server-into-one-row\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.silicloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to merge multiple rows in SQL Server into one row?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"name\":\"Silicon Cloud Blog\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\",\"name\":\"Silicon Cloud Blog\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"contentUrl\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"width\":1024,\"height\":1024,\"caption\":\"Silicon Cloud Blog\"},\"image\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\",\"https:\/\/twitter.com\/SiliCloudGlobal\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/f697031891aacefc4b681d139781d3c0\",\"name\":\"William Carter\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/1786698071dd8d74bec894b512f9e3c610c3a2a32985f67e688976cee3c8bbef?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/1786698071dd8d74bec894b512f9e3c610c3a2a32985f67e688976cee3c8bbef?s=96&d=mm&r=g\",\"caption\":\"William Carter\"},\"url\":\"https:\/\/www.silicloud.com\/blog\/author\/williamcarter\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How to merge multiple rows in SQL Server into one row? - Blog - Silicon Cloud","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.silicloud.com\/blog\/how-to-merge-multiple-rows-in-sql-server-into-one-row\/","og_locale":"en_US","og_type":"article","og_title":"How to merge multiple rows in SQL Server into one row?","og_description":"You can merge multiple lines into one by using the following method: To generate XML without using a specific tag name. SELECT STUFF((SELECT ',' + ColumnName FROM TableName FOR XML PATH('')), 1, 1, '') AS MergedColumn FROM TableName combine a string into one aggregated string SELECT STRING_AGG(ColumnName, ',') AS MergedColumn FROM TableName Utilize recursive CTE [&hellip;]","og_url":"https:\/\/www.silicloud.com\/blog\/how-to-merge-multiple-rows-in-sql-server-into-one-row\/","og_site_name":"Blog - Silicon Cloud","article_publisher":"https:\/\/www.facebook.com\/SiliCloudGlobal\/","article_published_time":"2024-03-15T18:13:58+00:00","article_modified_time":"2024-03-21T14:53:20+00:00","author":"William Carter","twitter_card":"summary_large_image","twitter_creator":"@SiliCloudGlobal","twitter_site":"@SiliCloudGlobal","twitter_misc":{"Written by":"William Carter","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.silicloud.com\/blog\/how-to-merge-multiple-rows-in-sql-server-into-one-row\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-merge-multiple-rows-in-sql-server-into-one-row\/"},"author":{"name":"William Carter","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/f697031891aacefc4b681d139781d3c0"},"headline":"How to merge multiple rows in SQL Server into one row?","datePublished":"2024-03-15T18:13:58+00:00","dateModified":"2024-03-21T14:53:20+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-merge-multiple-rows-in-sql-server-into-one-row\/"},"wordCount":79,"commentCount":0,"publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/blog\/how-to-merge-multiple-rows-in-sql-server-into-one-row\/","url":"https:\/\/www.silicloud.com\/blog\/how-to-merge-multiple-rows-in-sql-server-into-one-row\/","name":"How to merge multiple rows in SQL Server into one row? - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-15T18:13:58+00:00","dateModified":"2024-03-21T14:53:20+00:00","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-merge-multiple-rows-in-sql-server-into-one-row\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/how-to-merge-multiple-rows-in-sql-server-into-one-row\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/how-to-merge-multiple-rows-in-sql-server-into-one-row\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.silicloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to merge multiple rows in SQL Server into one row?"}]},{"@type":"WebSite","@id":"https:\/\/www.silicloud.com\/blog\/#website","url":"https:\/\/www.silicloud.com\/blog\/","name":"Silicon Cloud Blog","description":"","publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.silicloud.com\/blog\/#organization","name":"Silicon Cloud Blog","url":"https:\/\/www.silicloud.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","contentUrl":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","width":1024,"height":1024,"caption":"Silicon Cloud Blog"},"image":{"@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/SiliCloudGlobal\/","https:\/\/twitter.com\/SiliCloudGlobal"]},{"@type":"Person","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/f697031891aacefc4b681d139781d3c0","name":"William Carter","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/1786698071dd8d74bec894b512f9e3c610c3a2a32985f67e688976cee3c8bbef?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1786698071dd8d74bec894b512f9e3c610c3a2a32985f67e688976cee3c8bbef?s=96&d=mm&r=g","caption":"William Carter"},"url":"https:\/\/www.silicloud.com\/blog\/author\/williamcarter\/"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/19098","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/users\/8"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/comments?post=19098"}],"version-history":[{"count":1,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/19098\/revisions"}],"predecessor-version":[{"id":52821,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/19098\/revisions\/52821"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=19098"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=19098"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=19098"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}