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:
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.
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
