Home
> Sql Server > Comma separated values in mysql
Comma separated values in mysql
MY SQL:
GROUP_CONCAT function concatenates values within each group defined by GROUP BY clause.
Syntax | GROUP_CONCAT([DISTINCT] exp [ORDER BY sorting] [SEPARATOR ‘sep’]) |
Quick Example | SELECT GROUP_CONCAT(city) FROM cities GROUP BY state; |
Separator | Comma (,) by default, ” eliminates separator |
NULL Values | Skipped |
Max Length | 1024 by default, specified by group_concat_max_len system variable |
Sample:
CREATE TABLE cities ( city VARCHAR(70), state CHAR(2) ); INSERT INTO cities VALUES ('San Francisco', 'CA'); INSERT INTO cities VALUES ('San Diego', 'CA'); INSERT INTO cities VALUES ('Los Angeles', 'CA'); INSERT INTO cities VALUES ('Austin', 'TX'); INSERT INTO cities VALUES ('Houston', 'TX');
SELECT state, GROUP_CONCAT(city) FROM cities GROUP BY state; The result:
CA | San Francisco,San Diego,Los Angeles |
TX | Austin,Houston |
SQL Server:
SELECT state, STUFF((SELECT ',' + city FROM cities WHERE state = c.state FOR XML PATH('')),1 ,1 ,'') FROM cities c GROUP BY state Hope this help ! Rahul Bhatia
Categories: Sql Server
2013, comma, create, Database, FOR XML PATH, GROUP_CONCAT, Microsoft SQL Server, Microsoft Visual Studio, MySQL, SEPARATOR, Solution, SQL, SQL Server, stuff(), table
Comments (0)
Trackbacks (0)
Leave a comment
Trackback
Comments