SQL query to group tanks into one cell

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:

I want to add this tank_list column:

This is what I have here so far via the query below:
image

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!

You probably want GROUP_CONCAT or its equivalent in your DB brand.

Thank you. It looks like I’d need to use a Stored Procedure then?

No need for a stored procedure. GROUP_CONCAT returns a string as part of the grouped SELECT.

Hmm, I get the Group_concat' is not a recognized built-in function name. error.

What DB are you using?

MSSQL 2017 I believe

STRING_AGG is what you’re looking for, starting with v2017.

https://database.guide/the-sql-server-equivalent-to-group_concat/

2 Likes

Oops, actually it’s 2012. No STRING_AGG for me.

This seems to work for me; thanks for getting me on the right track… just don’t have the fancy builtin functions!

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