SQL Query help

Question

Hi,

I have the below table metadata...

TransactionID      Comments

1                         Very Bad

2                          Decent

1                         More to improve

3                         Nice

2                         Pretty cool

Now i need my output as the below format

TransactionID      Comments

1                         Very Bad,More to improve

2                          Decent,Pretty cool                      

3                         Nice

Can some one give query for the above scenario....???

Seems like i should use FOR XML ...

---

Mark as answer if the post help you... Regards, Indraneel A

Answer 1

CREATE  TABLE  #mable(mid INT, token nvarchar(16))

INSERT INTO #mable VALUES (0, 'foo')
INSERT INTO #mable VALUES(0, 'goo')
INSERT INTO #mable VALUES(1, 'hoo')
INSERT INTO #mable VALUES(1, 'moo')

SELECT m1.mid,
       ( SELECT m2.token + ','
           FROM #mable m2
          WHERE m2.mid = m1.mid
          ORDER BY token
            FOR XML PATH('') ) AS token
  FROM #mable m1
 GROUP BY m1.mid ;

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Uri has provided the solution even though he has missed out on the part to remove the extract comma from the result.

declare @tbl table
(
transactionid int,
comments varchar(50)
)
insert @tbl 
select 1,'very bad' union all
select 2,'Decent' union all
select 1,'More to improve' union all
select 3,'Nice' union all
select 2,'Pretty cool'
select transactionid,SUBSTRING((select ','+comments   from @tbl b where b.transactionid = a.transactionid for xml path('')),2,1000)
from @tbl a 
group by transactionid 

---

Murali Krishnan

Answer 3

Fix

SELECT  m1.mid,
        STUFF((SELECT ',' +  m2.token
                FROM #mable m2
          WHERE m2.mid = m1.mid
          ORDER BY token
               FOR XML PATH('')), 1, 1, '') AS users
 FROM #mable m1
 GROUP BY m1.mid 

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/