I'm working on a SQL to convert my data that looks like the table below to

I'm working on a SQL to convert my data that looks like the table below to

this

Any thoughts?

Very much appreciate your help.

I tried to look for some solution on various forums but can't find anything specific.

• sql
• snowflake-cloud-data-platform

• You want to use the LISTAGG function, don't you? See the documentation here – Jonas Metzler Commented Mar 6 at 5:16
• LISTAGG as the time of writing does not support "Cumulative window frame". Though it can be easily emulated: Requirement in Snowflake - cumulative string aggregation. In this case it will be: ARRAY_TO_STRING(ARRAY_AGG(Campaign) OVER(PARTITION BY Member ORDER BY Campaign),',') – Lukasz Szozda Commented Mar 6 at 8:47

3 Answers 3

We can handle this via a self join approach with the help of ROW_NUMBER():

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Member ORDER BY Campaign) rn
    FROM yourTable

)

SELECT
    t1.Member,
    t1.Campaign,
    LISTAGG(t2.Campaign) WITHIN GROUP (ORDER BY t2.Campaign) Pastcampaigns
FROM cte t1
INNER JOIN cte t2
    ON t2.Member = t1.Member AND
       t2.Campaign <= t1.Campaign
GROUP BY
    t1.Member,
    t1.Campaign
ORDER BY
    t1.Member,
    t1.Campaign;

The ROW_NUMBER() call adds a sequence to each campaign within each member. Then the self-join trick brings each row in connection with each previous campagin. We then aggregate by member and campaign to generate the CSV list of previous campaigns.

Demo

The demo is in MySQL, but the logic and data are identical as in Snowflake.

Another approach based on what is mentioned in comments using ARRAY_AGG and ARRAY_TO_STRING.

Sample query

SELECT 
member, campaign,
ARRAY_TO_STRING(ARRAY_AGG(campaign) OVER ( PARTITION BY member ORDER BY campaign  ), ',') AS past_campaigns
FROM campaigns
ORDER BY member, campaign;

Breaking it down

ARRAY_TO_STRING(ARRAY_AGG(campaign) OVER ( PARTITION BY member ORDER BY campaign  ), ',')

• It groups similar member(PARTITION BY member)
• Sorts it based on campaign(ORDER BY campaign)

ARRAY_AGG then combines the campaigns into an array within the group and ARRAY_TO_STRING converts the array to a string with comma separated values.

Output

-- create

CREATE TABLE EMPLOYEE (
  member NUMBER ,
  campaign varchar(4)
);

-- insert

INSERT INTO EMPLOYEE VALUES (1, 2006);
INSERT INTO EMPLOYEE VALUES (1, 2007);
INSERT INTO EMPLOYEE VALUES (1, 2008);
INSERT INTO EMPLOYEE VALUES (2, 2023);
INSERT INTO EMPLOYEE VALUES (2, 2024);

-- fetch 

with temp as(
select member, campaign, 
listagg(campaign,',') within group( order by campaign ) 
over (partition by member) as val ,
rank() over(partition by member order by campaign asc) as rnk
from employee
)
select member, campaign,
case when INSTR(val, ',', 1, rnk)=0 then val
else
substr(val,1,INSTR(val, ',', 1, rnk)-1) end as pastcampaigns
from temp;