splitting rows in Redshift

Question

In my table the data is as below:

col1    col2    col3    col4
A1      A2      A3      4
B1      B2      B3      3   
C1      C2      C3      1

I need the output as below:

col1    col2    col3    col4
A1      A2      A3      1
A1      A2      A3      2
A1      A2      A3      3
A1      A2      A3      4
B1      B2      B3      1   
B1      B2      B3      2   
B1      B2      B3      3   
C1      C2      C3      1

Im using Redshift DB.

Thanks for being specific about the fact that you're using Redshift (so most of the usual solutions don't apply). What's the logic (in words) behind how you get from the input to the expected output? It looks like *For col4=n, repeat col1, col2 and col3 n times, producing tuples like (col1, col2, col3, 1 .. n)* ... in which case you want to JOIN on `generate_series`. If you can do that in Redshift. (I'll start answering Redshift questions when Amazon donates a Redshift account to http://sqlfiddle.com/ and not before). — Craig Ringer, Apr 01 '14 at 07:15

score 4 · Answer 1 · answered Apr 11 '14 at 15:29

You're correct, Redshift currently doesn't support generate_series. One way to get around this is to generate your own series table and join to that. In my example below I just did a row_number() against the pg_attribute table to generate the sequence. You can adjust TOP (v) value to adjust now many numbers you want in your sequence, if you need more than what pg_attribute can give you, try cross joining pg_attribute with itself. I don't claim this to be the best way to generate a sequence table, you can generate it any way you want; my main point is that you'll need one to substitute for generate_series.

Once you have your series table, then its a simple join to get your result. Complete Example:

-- Setup Example
CREATE TABLE test
(
    col1 char(2),
    col2 char(2),
    col3 char(2),
    col4 integer
);

INSERT INTO test(col1, col2, col3, col4)
VALUES 
    ('A1', 'A2', 'A3', 4),
    ('B1', 'B2', 'B3', 3),
    ('C1', 'C2', 'C3', 1);


-- Generate 10 sequence numbers to table.  Adjust as needed or roll your own
SELECT TOP 10 ROW_NUMBER() OVER (PARTITION BY attnum ORDER BY attnum) n
INTO sequence
FROM pg_catalog.pg_attribute;

-- Example Query
SELECT col1, col2, col3, s.n
FROM test t
     INNER JOIN sequence s ON s.n <= t.col4
ORDER BY col1, col2, col3, s.n;

-- Clean up
DROP TABLE sequence;
DROP TABLE test;

splitting rows in Redshift

1 Answers1

Linked