create one concatenated value out of multiple rows-CodePudding

See reproducible example below.

WHAT NEEDS TO BE DONE

See comments in code: creating unique totes using '2021-01-01'(column: Date) and 'A'(column: Tote, while creating a concatenated string from 'hat'(column: content) and 'cat'(column: content) in alphabetic order.

Reproducible example:

CREATE OR REPLACE TABLE
`first_table` (
  `Date` string NOT NULL,
  `TotearrivalTimestamp` string  NOT NULL,
  `Tote` string NOT NULL,
  `content` string NOT NULL,
  `location` string NOT NULL,
);
INSERT INTO `first_table` (`Date`, `TotearrivalTimestamp`, `Tote`, `content`, `location`) VALUES
  ('2021-01-01', '13:00','A','hat','1'), #first tote
  ('2021-01-01', '13:00','A','cat','1'), #first tote
  ('2021-01-01', '14:00', 'B', 'toy', '1'),
  ('2021-01-01', '14:00', 'B', 'cat', '1'),
  ('2021-01-01', '15:00', 'A', 'toy', '1'),
  ('2021-01-01', '13:00', 'A', 'toy', '1'),
  ('2021-01-02', '13:00', 'A', 'hat', '1'),
  ('2021-01-02', '13:00', 'A', 'cat', '1');
  

CREATE OR REPLACE TABLE
`desired_result` (
  `Date` string NOT NULL,
  `Tote` string NOT NULL,
  `TotearrivalTimestamp` string  NOT NULL,
  `content_combination` string NOT NULL,
 );
INSERT INTO `desired_result` (`Date`, `Tote`, `TotearrivalTimestamp`, `content_combination`) VALUES

('2021-01-01', 'A', '13:00', 'cathat'), #first tote
('2021-01-01', 'B', '14:00', 'cattoy'),
('2021-01-01', 'A', '15:00', 'toy'),
('2021-01-02', 'A', '13:00', 'cathattoy');

CodePudding user response：

From the backticks I conclude this is MySQL. In MySQL the string aggregation function is called GROUP_CONCAT:

select
  date, tote,totearrivaltimestamp,
  group_concat(content order by content separator '') as content_combination
from mytable
group by date, tote,totearrivaltimestamp
order by date, tote,totearrivaltimestamp;

CodePudding user response：

STRING_AGG() did the trick - worked for me