SQL help, Get the desired output

Question

Input:

+---------+---------+--------+
| row_min | row_max | tCount |
+---------+---------+--------+
|       2 |       4 |      1 |
|       7 |      10 |      2 |
|      13 |      14 |      3 |
+---------+---------+--------+

Required Output:

+-----+--------+
| row | tcount |
+-----+--------+
|   2 |      1 |
|   3 |      1 |
|   4 |      1 |
|   7 |      2 |
|   8 |      2 |
|   9 |      2 |
|  10 |      2 |
|  13 |      3 |
|  14 |      3 |
+-----+--------+

row_min and row_max are expanded in the output with corresponding tcount in their range This step is required as a part of data transformations, I need to do on a dataset using SQL (data residing in Amazon redshift). I am stuck on this particular step. Please provide the SQL code required for the same, hopefully limited to using joins and analytic functions.

Answer 1

You can do it using a tally table large enough to include numbers as a high as MAX(row_max) of your table:

WITH Tally AS (
   SELECT ROW_NUMBER() OVER() AS n
   FROM (
      SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
      SELECT 1 UNION ALL SELECT 1 UNION ALL
      SELECT 1 UNION ALL SELECT 1 UNION ALL 
      SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ) x(n)
   CROSS JOIN (
      SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
      SELECT 1 UNION ALL SELECT 1 UNION ALL
      SELECT 1 UNION ALL SELECT 1 UNION ALL 
      SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ) y(n)
)
SELECT n, tCount
FROM Tally AS t
INNER JOIN mytable AS m ON t.n >= m.row_min AND t.n <= m.row_max

I think Redshift supports simple, non-recursive, CTEs, so the above should work.

Demo here