# How to use SQL version of STUFF and groupby in elastic search **URL:** \ **Category:** Elasticsearch\ **Created:** [October 11, 2016, 3:00am UTC](https://discuss.elastic.co/t/how-to-use-sql-version-of-stuff-and-groupby-in-elastic-search/62679 "2016-10-11T03:00:26Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![mohit\_nagpal](https://avatars.discourse-cdn.com/v4/letter/m/f14d63/32.png) [@mohit\_nagpal](https://discuss.elastic.co/u/mohit_nagpal)\ **Post date:** [October 11, 2016, 3:00am UTC](https://discuss.elastic.co/t/how-to-use-sql-version-of-stuff-and-groupby-in-elastic-search/62679/1 "2016-10-11T03:00:26Z")
For example assume mapping is following : ID User Color 1 one blue 2 one black 3 two orange 4 two yellow Needed Result is. ID User Color 1 one blue, black 2 two orange, yellow In case of SQL this can be done by GroupBy and STUFF. How this can be done in ES. ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [October 11, 2016, 3:07am UTC](https://discuss.elastic.co/t/how-to-use-sql-version-of-stuff-and-groupby-in-elastic-search/62679/2 "2016-10-11T03:07:19Z")
If you are grouping by `User` then just do a terms aggregation on that - [https://www.elastic.co/guide/en/elasticsearch/reference/2.4/search-aggregations-bucket-terms-aggregation.html](https://www.elastic.co/guide/en/elasticsearch/reference/2.4/search-aggregations-bucket-terms-aggregation.html) ---
**Author:** ![mohit\_nagpal](https://avatars.discourse-cdn.com/v4/letter/m/f14d63/32.png) [@mohit\_nagpal](https://discuss.elastic.co/u/mohit_nagpal)\ **Post date:** [October 11, 2016, 3:15am UTC](https://discuss.elastic.co/t/how-to-use-sql-version-of-stuff-and-groupby-in-elastic-search/62679/3 "2016-10-11T03:15:38Z")
This will just give count. How to merge column values. ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [October 11, 2016, 5:11am UTC](https://discuss.elastic.co/t/how-to-use-sql-version-of-stuff-and-groupby-in-elastic-search/62679/4 "2016-10-11T05:11:56Z")
You'll probably need to do that externally once you have the buckets. ---
**Author:** ![mohit\_nagpal](https://avatars.discourse-cdn.com/v4/letter/m/f14d63/32.png) [@mohit\_nagpal](https://discuss.elastic.co/u/mohit_nagpal)\ **Post date:** [October 14, 2016, 6:20am UTC](https://discuss.elastic.co/t/how-to-use-sql-version-of-stuff-and-groupby-in-elastic-search/62679/5 "2016-10-14T06:20:11Z")
Thanks for suggestion. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 5, 2017, 10:12pm UTC](https://discuss.elastic.co/t/how-to-use-sql-version-of-stuff-and-groupby-in-elastic-search/62679/6 "2017-07-05T22:12:27Z")