CODEWITHSUNDEEP
sqlite
BlackSwan
BlackSwan

Reputation: 25

Intentionally make duplicate rows in sqlite select query

I have a query that looks like this:

SELECT a, b, c FROM t1;

I would like to modify the statement so I receive a response that looks something like:

a, b
a, b
.
.
.
a, b

where I get the same response (a, b) the same number of times as the value of c.

Upvotes: 1

Views: 200

Answers (1)

CL.
CL.

Reputation: 180020

This requires a recursive common table expression:

WITH RECURSIVE repeated(a, b, count) AS (
  SELECT a, b, c FROM t1 WHERE c > 0
  UNION ALL
  SELECT a, b, count - 1 FROM repeated WHERE count > 1
)
SELECT a, b FROM repeated;

Upvotes: 2

Related Questions