CODEWITHSUNDEEP
snowflake-cloud-data-platform
Robl09
Robl09

Reputation: 41

How iterate through table and stuff/concatenate rows into one column based on column value in snowsql?

The goal is that if the message begins with "V22200" (ex. row 2) I want to stuff/concatenate each of subsequent rows until the next "V22200" into a new column labeled "Transaction" (Only the message data would be stuffed Basically pivoting ROWID 2-8 each into a separate column.

The next occurrence of "V22200" indicates a new transaction.

I need to loop through the entire dataset - how do I write this in SNOWSQL in snowflake?

enter image description here

Upvotes: 1

Views: 511

Answers (1)

Greg Pavlik
Greg Pavlik

Reputation: 11086

You can use the conditional_true_event window function to form groups, and then use those groups to listagg:

create or replace table T1 (rowid int, message string, filename string);

insert into T1 (rowid, message, filename) values
(1, 'foo', 'FILE1'),
(2, 'V22200A blah, blah', 'FILE1'),
(3, 'bar', 'FILE1'),
(4, 'bar2', 'FILE1'),
(5, 'V22200A blah, blah', 'FILE1'),
(6, 'Hello world', 'FILE1');

with GROUPS as
(
select   ROWID
        ,MESSAGE
        ,FILENAME
        ,conditional_true_event(left(MESSAGE, 6) = 'V22200') 
             over (partition by FILENAME order by ROWID) GROUPING
from     T1
)
select   min(ROWID) as MIN_ROWID
        ,max(ROWID) as MAX_ROWID
        ,LISTAGG(MESSAGE, ',') as MESSAGES
        ,FILENAME
from     GROUPS
group by FILENAME, GROUPING
order by MIN_ROWID
;

Upvotes: 1

Related Questions