ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.Concatenate multiple Rows into single Row with comma separated string in SQL Server<p>Hi <a class="username" rel="telldurges"> telldurges</a>,</p> <p>Refer below sample query.</p> <p><span style="text-decoration: underline;"><strong>SQL</strong></span></p> <pre class="brush: sql">DECLARE @Test AS TABLE(Bookid INT, Bookname VARCHAR(MAX), libid INT, libname VARCHAR(MAX)) INSERT INTO @Test VALUES(334,&#39;physics&#39;,1,&#39;LIB1&#39;) INSERT INTO @Test VALUES(336,&#39;math&#39;,1,&#39;LIB1&#39;) INSERT INTO @Test VALUES(6468,&#39;science&#39;,1,&#39;LIB1&#39;) INSERT INTO @Test VALUES(357,&#39;hindi&#39;,3,&#39;LIB3&#39;) INSERT INTO @Test VALUES(358,&#39;eng&#39;,3,&#39;LIB3&#39;) SELECT libid, Bookid = STUFF ( &nbsp;&nbsp; &nbsp;( &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT DISTINCT &#39;, &#39;+ CAST(t.Bookid AS VARCHAR(MAX)) &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM @Test t &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE t.libid = t1.libid &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FOR XMl PATH(&#39;&#39;) &nbsp;&nbsp;&nbsp; ),1,1,&#39;&#39; &nbsp; ),Libname, Bookname = STUFF &nbsp; ( &nbsp; &nbsp;&nbsp;&nbsp; ( &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT DISTINCT &#39;, &#39;+ CAST(t.Bookname AS VARCHAR(MAX)) &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM @Test t &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE t.libid = t1.libid &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FOR XMl PATH(&#39;&#39;) &nbsp;&nbsp;&nbsp; ),1,1,&#39;&#39; &nbsp; ) &nbsp; FROM @Test t1 &nbsp; GROUP BY libid ,Libname</pre> <p><span style="text-decoration: underline;"><strong>Output</strong></span></p> <table border="1"> <tbody> <tr> <td><strong>libid</strong></td> <td><strong>Bookid</strong></td> <td><strong>Libname</strong></td> <td><strong>Bookname</strong></td> </tr> <tr> <td>1</td> <td>334, 336, 6468</td> <td>LIB1</td> <td>math, physics, science</td> </tr> <tr> <td>3</td> <td>357, 358</td> <td>LIB3</td> <td>eng, hindi</td> </tr> </tbody> </table> <p>&nbsp;</p>https://www.aspforums.net:443/Threads/189902/Concatenate-multiple-Rows-into-single-Row-with-comma-separated-string-in-SQL-Server/https://www.aspforums.net:443/Threads/189902/Concatenate-multiple-Rows-into-single-Row-with-comma-separated-string-in-SQL-Server/Fri, 07 Jun 2019 01:30:20 GMT