0

I'm attempting to concatenate names from the Name column based on start time found in the Start Time column as found in the following Table Data.

The end result should concatenate the name from rows 1 (Holiday Sale) and 2 (New Year) because the Start Time's fall on the same date and time. So it should read similar to "Holiday Sale, New Year" with the Start Date column showing "2019-12-26 00:00:00".

I've tried to alter several queries that were originally used to merge dates based on a name or ID so that they would merge (concatenate) names based on the dates. None of these proved successful and resulted in a "Timeout Error". Here's a couple of queries I've attempted: - start date end date combine rows - Merge overlapping date intervals

Alex Lohser
  • 3
  • 2

1 Answers1

0

I believe LISTAGG https://docs.snowflake.net/manuals/sql-reference/functions/listagg.html is something you are looking for. Try 

select listagg(name, ', '), start_time from table group by start_time;
Jiaxing Liang
  • 152
  • 10
  • Yes that works! Additionally, would the code be similar if I was trying to have it be concatenated base on both the start time and the end time? For example, if I was trying to display the end result as a column chart. I would need the x-axis as the date range and the y-axis would show me which names (promotions) appeared on each day and when those promotions stopped. – Alex Lohser Jan 15 '20 at 16:40
  • 1
    So I figured this second portion out by performing an inner join on a base date table. select .bd.date, listagg(name, ', ') as "NAME" from base_date bd inner join table t on t."START" <= bd.date AND t."END" >= bd.date group by 1 order by bd.date – Alex Lohser Jan 15 '20 at 18:49