I have data from a PostgreSQL query in this basic shape:

CREATE TABLE Table1
(`Group` varchar(1), `Months` int, `Value` int);

INSERT INTO Table1
(`Group`, `Months`, `Value`)
VALUES
('A', 11, 137),
('B', 9, 666),
('C', 3, 42);

Assume that Value does not change for each combination of Group and Month. What I want to do is, expand the table so that Months is a range from 1 to whatever the Months value is, for each group. So the output would look like:

CREATE TABLE DesiredOutput
(`Group` varchar(1), `Months` int, `Value` int);

INSERT INTO DesiredOutput
(`Group`, `Months`, `Value`)
VALUES
('A', 1, 137),
('A', 2, 137),
('A', 3, 137),
('A', 4, 137),
('A', 5, 137),
('A', 6, 137),
('A', 7, 137),
('A', 8, 137),
('A', 9, 137),
('A', 10, 137),
('A', 11, 137),
('B', 1, 666),
('B', 2, 666),
('B', 3, 666),
('B', 4, 666),
('B', 5, 666),
('B', 6, 666),
('B', 7, 666),
('B', 8, 666),
('B', 9, 666),
('C', 1, 42),
('C', 2, 42),
('C', 3, 42);

Is there an easy way to do this with SQL, or am I going to have to bring an outside scripting language into the mix?

CodePudding user response：

Using a sequence table we can try:

WITH cte AS (
    SELECT * FROM generate_series(1, 12) AS s(Months)
)

SELECT t1."Group", t2.Months, t1.Value
FROM Table1 t1
INNER JOIN cte t2
    ON t2.Months <= t1.Months
ORDER BY t1."Group", t2.Months;

CodePudding user response：

As you need a time series, you can with Postgres easy make one, by using generate_series in an CTE.

A CROSS JOIN with a WHERE clause that only allows months as high as the the Months that are represented in the Base table. Of course you can, if you have a more complicated pattern, you can creaet your own `WHERE" clause

WITH CTE AS (
    SELECT * FROM generate_series(1, 12) AS s(Months)
)
SELECT t1."Group", CTE.months, t1."Value"
FROM Table1 t1 CROSS JOIN CTE
WHERE CTE.months <= t1."Months"
ORDER BY "Group",months

Group | months | Value
:---- | -----: | ----:
A     |      1 |   137
A     |      2 |   137
A     |      3 |   137
A     |      4 |   137
A     |      5 |   137
A     |      6 |   137
A     |      7 |   137
A     |      8 |   137
A     |      9 |   137
A     |     10 |   137
A     |     11 |   137
B     |      1 |   666
B     |      2 |   666
B     |      3 |   666
B     |      4 |   666
B     |      5 |   666
B     |      6 |   666
B     |      7 |   666
B     |      8 |   666
B     |      9 |   666
C     |      1 |    42
C     |      2 |    42
C     |      3 |    42

db<>fiddle here

CodePudding user response：

You don't need to use a CTE and you don't need to use a CROSS JOIN.

This will do:

SELECT t."Group", m.mnth AS "Months", t."Value"
FROM Table1 t
  INNER JOIN GENERATE_SERIES(1,12) AS m(mnth) ON t."Months" >= m.mnth
ORDER BY t."Group", m.mnth;