Concatenate rows into a comma-separated list,we can do it like this.<\/p>\n
[tsql]
\nSELECT DISTINCT
\n\tusr.id, usr.name,
\n\t(SELECT STUFF((SELECT ‘,’+title FROM hobby_main AS hm WHERE hm.uid=usr.id FOR XML PATH(”)),1,1,”)) AS hobbies
\nFROM user AS usr
\nWHERE name = ‘Pengcheng’
\n[\/tsql]<\/p>\n
I help my little brother to rank the running data in CampusRun app,there is a release date for this app.It calculates total distance from the release date when user’s registration date before the app release date,then calculates total distance from user’s registration date.so i have to use IF statement inside WHERE clause in SQL like below.<\/p>\n
[mysql]
\nSELECT b.id, b.cnname, SUM(distance)\/1000 AS total_distance
\nFROM `xm__run_infos_by_day` AS a
\n\tINNER JOIN `xm__member` AS b
\n\tON a.ydq_openid = b.ydq_openid
\nWHERE IF(b.ydq_join_time < '1467450000', a.time >= ‘1467450000’, a.time >= b.ydq_join_time)
\nGROUP BY a.ydq_openid, b.cnname, b.id
\nORDER BY SUM(distance)\/1000 DESC
\n[\/mysql]<\/p>\n","protected":false},"excerpt":{"rendered":"
SQL Server Concatenate rows into a comma-separated list<\/p>\n