Scenario based SQL Interview Question

I have come across the below scenario in one of the forums and I'm giving the solution here. 

SRC:
Have source data like below

Output should like below:

what is the SQL to get output as shown above?

Ans: This can be achieved using loops in SQL and below is the query: 

WITH t AS (SELECT col1, col2 v FROM src)
  SELECT CEIL (v) - i AS seq, col1,
         CASE WHEN (i = 0 AND CEIL (v) <> v) 
              THEN 1 + v - (CEIL (v) - i)
              ELSE 1
         END  AS COL2
  FROM t, 
    XMLTABLE ('for $i in 0 to xs:int(D) return $i'
    PASSING XMLELEMENT (d, CEIL (v) - 1) COLUMNS i INTEGER PATH '.')
ORDER BY col1, CEIL (v) - i 



Please refer the below link to know more about loops: 
http://www.dba-oracle.com/t_loop_sql.htm