This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Last week on my Facebook Page, I have received a couple message about what is a similar solution of SQL Server STUFF () in PostgreSQL.
Most of the Database Developers require to perform String Aggregation based on different group of records.
Since PostgreSQL 9.0, STRING_AGG(expression, delimiter) function is available to perform String Aggregation operation.
Using STRING_AGG(), We can concatenate strings using different type of delimiter symbols.
Example of STRING_AGG():
Create a sample Students table:
|
1 2 3 4 5 6 |
CREATE TABLE tbl_Students ( StudID INT ,StudName CHARACTER VARYING ,StudGrades CHAR(1) ); |
Insert few sample records:
|
1 2 3 4 5 6 |
INSERT INTO tbl_Students VALUES (1,'Anvesh','A'),(2,'Kimly','B') ,(3,'Jenny','C'),(4,'Ali','B') ,(5,'Mukesh','D'),(6,'Sofia','A') ,(7,'Roy','C'),(8,'Martin','C'); |
Concatenate Students Name per each Student Grade and arrange by Grade wise row (Using STRING_AGG()):
|
1 2 3 4 5 6 |
SELECT StudGrades ,STRING_AGG(StudName,', ') AS StudPerGrade FROM tbl_Students GROUP BY StudGrades ORDER BY 1 ; |
The Result:
|
1 2 3 4 5 6 7 |
studgrades | studpergrade ------------+-------------------- A | Anvesh, Sofia B | Kimly, Ali C | Jenny, Roy, Martin D | Mukesh (4 rows) |
