# SQL query to group tanks into one cell **URL:** \ **Category:** Ignition\ **Tags:** database\ **Created:** [August 16, 2019, 12:42am UTC](https://forum.inductiveautomation.com/t/sql-query-to-group-tanks-into-one-cell/27499 "2019-08-16T00:42:09Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![mriederer](https://avatars.discourse-cdn.com/v4/letter/m/4af34b/32.png) [@mriederer](https://forum.inductiveautomation.com/u/mriederer)\ **Post date:** [August 16, 2019, 12:42am UTC](https://forum.inductiveautomation.com/t/sql-query-to-group-tanks-into-one-cell/27499/1 "2019-08-16T00:42:09Z")
I’m fairly new to SQL and would appreciate any help. Sorry if this isn’t really an Ignition thing but I figured someone on here could help out. Say I have a bunch of tanks with different fruit and either fresh or rotten. I am trying to group tanks by fruit, summarize the volume for both fresh and rotten types, and then… the problem I want to solve is having the listed tanks added to the query. This might involve a JOIN statement? So far I have my basic table here: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/2/2dce7b23215fa71879299c613140878c3e046d36.png) I want to add this tank\_list column: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/9/9b927125209137be72b93f5f4bfbd5542e687837.png) This is what I have here so far via the query below: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/2/272da0d1086dc762de7356f7f50f859af4025818.png) ```auto SELECT column2, column4, SUM(column3) AS Volume FROM table_name GROUP BY column2, column4 ORDER BY column2, CASE WHEN column4 = 'fresh' THEN 1 WHEN column4 = 'rotten' THEN 2 END ``` Thanks for any help from the SQL experts! ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [August 16, 2019, 11:39am UTC](https://forum.inductiveautomation.com/t/sql-query-to-group-tanks-into-one-cell/27499/2 "2019-08-16T11:39:34Z")
You probably want [GROUP\_CONCAT](https://mariadb.com/kb/en/library/group_concat/) or its equivalent in your DB brand. ---
**Author:** ![mriederer](https://avatars.discourse-cdn.com/v4/letter/m/4af34b/32.png) [@mriederer](https://forum.inductiveautomation.com/u/mriederer)\ **Post date:** [August 19, 2019, 7:36pm UTC](https://forum.inductiveautomation.com/t/sql-query-to-group-tanks-into-one-cell/27499/3 "2019-08-19T19:36:33Z")
Thank you. It looks like I’d need to use a Stored Procedure then? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [August 19, 2019, 8:04pm UTC](https://forum.inductiveautomation.com/t/sql-query-to-group-tanks-into-one-cell/27499/4 "2019-08-19T20:04:02Z")
No need for a stored procedure. GROUP\_CONCAT returns a string as part of the grouped SELECT. ---
**Author:** ![mriederer](https://avatars.discourse-cdn.com/v4/letter/m/4af34b/32.png) [@mriederer](https://forum.inductiveautomation.com/u/mriederer)\ **Post date:** [August 19, 2019, 8:44pm UTC](https://forum.inductiveautomation.com/t/sql-query-to-group-tanks-into-one-cell/27499/5 "2019-08-19T20:44:12Z")
Hmm, I get the `Group_concat' is not a recognized built-in function name.` error. ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [August 19, 2019, 8:45pm UTC](https://forum.inductiveautomation.com/t/sql-query-to-group-tanks-into-one-cell/27499/6 "2019-08-19T20:45:19Z")
What DB are you using? ---
**Author:** ![mriederer](https://avatars.discourse-cdn.com/v4/letter/m/4af34b/32.png) [@mriederer](https://forum.inductiveautomation.com/u/mriederer)\ **Post date:** [August 19, 2019, 8:47pm UTC](https://forum.inductiveautomation.com/t/sql-query-to-group-tanks-into-one-cell/27499/7 "2019-08-19T20:47:09Z")
MSSQL 2017 I believe ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [August 19, 2019, 8:49pm UTC](https://forum.inductiveautomation.com/t/sql-query-to-group-tanks-into-one-cell/27499/8 "2019-08-19T20:49:32Z")
STRING\_AGG is what you’re looking for, starting with v2017. [https://database.guide/the-sql-server-equivalent-to-group\_concat/](https://database.guide/the-sql-server-equivalent-to-group_concat/) ---
**Author:** ![mriederer](https://avatars.discourse-cdn.com/v4/letter/m/4af34b/32.png) [@mriederer](https://forum.inductiveautomation.com/u/mriederer)\ **Post date:** [August 19, 2019, 9:43pm UTC](https://forum.inductiveautomation.com/t/sql-query-to-group-tanks-into-one-cell/27499/9 "2019-08-19T21:43:02Z")
Oops, actually it’s 2012. No STRING\_AGG for me. ---
**Author:** ![mriederer](https://avatars.discourse-cdn.com/v4/letter/m/4af34b/32.png) [@mriederer](https://forum.inductiveautomation.com/u/mriederer)\ **Post date:** [August 19, 2019, 11:33pm UTC](https://forum.inductiveautomation.com/t/sql-query-to-group-tanks-into-one-cell/27499/10 "2019-08-19T23:33:44Z")
This seems to work for me; thanks for getting me on the right track… just don’t have the fancy builtin functions! ```auto SELECT m.column2, m.column4, SUM(m.column3) as 'Volume', tank_list = STUFF(( SELECT ', ' + tn.column1 FROM dbo.table_name tn WHERE m.column2 = tn.column2 and m.column4= tn.column4 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') FROM dbo.table_name m JOIN dbo.table_name s ON s.column2 = m.column2 and s.column4 = m.column4 and s.column1 = m.column1 GROUP BY m.column2, m.column4 ORDER BY m.column2, CASE WHEN m.column4 = 'fresh' THEN 1 WHEN m.column4 = 'rotten' THEN 2 END, m.column4 ```