How to convert data into this form in SQL :

Question

Input:

Input

I have to Convert data from input to output. where we trying if for id freq is n then create n rows of that id.

Output:

output

Presto <> MySQL. I removed these conflicting tags, please retag only the one database that you running. — GMB, Aug 03 '20 at 09:47
I can't see anything in your images, just empty boxes. Anyway, please, please don't post code and data as (link to) images, but as formatted text instead — HoneyBadger, Aug 03 '20 at 09:48

score 2 · Accepted Answer · edited Aug 03 '20 at 21:23

2

In Presto, one option uses sequence() and a lateral join to generate the rows:

select t.id, x.value
from mytable t
cross join lateral unnest(sequence(1, t.freq)) as x(value)
order by t.id, x.value

edited Aug 03 '20 at 21:23

Piotr Findeisen

19,480
2
52
82

answered Aug 03 '20 at 09:54

GMB

216,147
25
84
135

If you could provide some sort of _DB fiddle_ that would be awesome. – Abra Aug 03 '20 at 09:57
@Abra: unfortunately, there is no Presto fiddle that I know about... – GMB Aug 03 '20 at 09:58
1

@Abra, GMB re fiddle -- see https://twitter.com/findepi/status/1281329811026305030. I am happy to help with Presto side if there is interest. – Piotr Findeisen Aug 03 '20 at 21:24

score 1 · Answer 2 · answered Aug 03 '20 at 09:54

Great question - one approach is to use a table of integers - if you are certain there will never be more than (say) 128 repeats you can create the table of integers as follows:

CREATE TABLE nums(i INT PRIMARY KEY);
INSERT INTO nums VALUES (1);
INSERT INTO nums SELECT 1+i FROM nums;
INSERT INTO nums SELECT 2+i FROM nums;
INSERT INTO nums SELECT 4+i FROM nums;
INSERT INTO nums SELECT 8+i FROM nums;
INSERT INTO nums SELECT 16+i FROM nums;
INSERT INTO nums SELECT 32+i FROM nums;
INSERT INTO nums SELECT 64+i FROM nums;

Now you can use that table:

SELECT id, i AS value
 FROM input JOIN nums ON i BETWEEN 1 AND Freq

score 1 · Answer 3 · answered Aug 03 '20 at 09:58

I would do that with Lateral as GMB showed. In SQL server however it could be done in another way like below as an example:

with tally as 
(
  select top(select max(v) from myTable)
  ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
            FROM Master.sys.All_Columns t1
            CROSS JOIN Master.sys.All_Columns t2
)
select m.id, t.N
from tally t
cross join myTable m 
where t.N <= m.v
order by m.id, t.N;

DBFiddle demo

How to convert data into this form in SQL :

3 Answers3