Generate row based on zip code start and end range value in snowflake using SQL

Question

Sample Data:

Code using SQL only no stored proc.

Card No	StartZip	EndZip
786****	97000	97003
787****	98000	98003

Expected output

Card No	ZipCode
786***	97000
786***	97001
786***	97002
786***	97003
787***	98000
787***	98001
787***	98002
787***	98003

Answer 1

Try something like this:

WITH nums AS (
    SELECT ROW_NUMBER() OVER (ORDER BY seq4())-1 AS num
    FROM TABLE(generator(rowcount => 100000))
), input AS (
    SELECT Card_No, StartZip, EndZip
      FROM (VALUES ('786****',  97000,  97003),
                   ('787****',  98000,  98003)) t(Card_No, StartZip, EndZip)
)
SELECT i.Card_No, i.StartZip, i.EndZip, i.StartZip + n.num AS ZipCode
  FROM input AS i
  LEFT JOIN nums AS n ON i.StartZip + n.num <= i.EndZip
 ORDER BY i.Card_No, ZipCode;

Answer 2

In Snowflake you can also write a recursive CTE to avoid joins:

WITH RECURSIVE ZipCodeRange AS (
  SELECT CardNo, StartZip AS ZipCode, EndZip
  FROM <NAME OF YOUR TABLE>
  UNION ALL
  SELECT z.CardNo, z.ZipCode + 1 AS ZipCode, z.EndZip
  FROM ZipCodeRange z
  WHERE z.ZipCode < z.EndZip
)
SELECT CardNo, ZipCode
FROM ZipCodeRange
ORDER BY CardNo, ZipCode;

Answer 3

You could use a lateral join on flattened array of all zip codes generated using array_generate_range

 select card_no, b.value as zip_code
 from t, lateral flatten(array_generate_range(start_zip, end_zip + 1)) b