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
  • 732,580
  • 175
  • 1,330
  • 1,459
Hemanthkumar Naik
  • 151
  • 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
  • 37,315
  • 5
  • 22
  • 31
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()

THE LIFE-TIME LEARNER
  • 1,476
  • 1
  • 8
  • 18