asp.net - use gather of a column with Group By in sql -

i have table in sql store statistics of visits, , have below columns:

referredurl

searchedword(words lead user page)

hittime

i want report of table below column: - refferedurl - recenreferdate - allsearchedwors

select referredurl , max (hittime) 'recent refer date' stattbl group referredurl 

i have problem gathering allsearchedwords, how can gather of searchedword of group , use column?

if you're using sql server try

select t.referredurl,         max (t.hittime) 'recent refer date',        (          stuff((select distinct ',' + searchedword                    stattbl                   referredurl = t.referredurl                     xml path('')) , 1 , 1 , '' )         ) 'searched words'   stattbl t  group t.referredurl; 

if need words present duplicates ditch distinct subquery.

here sqlfiddle example sql server

if mysql then

select t.referredurl,         max(t.hittime) 'recent refer date',        group_concat(distinct(searchedword)) 'searched words'   stattbl t  group t.referredurl; 

here sqlfiddle example mysql

sample data

| referredurl |                    hittime | searchedword | ----------------------------------------------------------- |        url1 | may, 22 2013 00:00:00+0000 |        apple | |        url1 | may, 22 2013 12:00:00+0000 |       banana | |        url1 | may, 22 2013 18:00:00+0000 |         pear | |        url1 | may, 22 2013 18:05:00+0000 |        apple | |        url2 | may, 22 2013 23:00:00+0000 |        apple | 

sample output

| referredurl |          recent refer date |    searched words | ---------------------------------------------------------------- |        url1 | may, 22 2013 18:05:00+0000 | apple,banana,pear | |        url2 | may, 22 2013 23:00:00+0000 |             apple |