0

I have a table A:

enter image description here

and I want an output like this:

enter image description here.

I want to concatenate multiple rows into a single row as well as I want to count how many rows are concatenated..

thanks

marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425
Hemanthkumar Naik
  • 141
  • 1
  • 3
  • 11
  • 1
    Does this answer your question? [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) – Ed Bangga Nov 22 '19 at 07:50

2 Answers2

4

use stuff()

select id,  stuff(( select concat( ',', name) from tablename b where a.id= b.id
for xml path('')),1,1, ''),count(*) as cnt
    from tablename a
    group by id
Fahmi
  • 36,607
  • 5
  • 19
  • 28
2
CREATE TABLE #Temp
(ID INT,NAME VARCHAR(50))


INSERT INTO #Temp(ID, [NAME])VALUES(1,'ABC')
INSERT INTO #Temp(ID, [NAME])VALUES(1,'EFG')
INSERT INTO #Temp(ID, [NAME])VALUES(2,'HIJ')
INSERT INTO #Temp(ID, [NAME])VALUES(2,'JKL')
INSERT INTO #Temp(ID, [NAME])VALUES(3,'MNO')

First Created a table....

SELECT t.ID,STUFF(
(
SELECT ',' + s.NAME
FROM #Temp s
WHERE s.ID = t.ID
FOR XML PATH('')),1,1,'') AS NAME,COUNT(t.ID) AS COUNT

FROM #Temp AS t
GROUP BY ID

USE Stuff()

Akhilesh Singh
  • 1,440
  • 1
  • 7
  • 17