ParoX ParoX - 24 days ago 7
SQL Question

Duplicate rows using left join

Suppose you have a table like this:

+----+--------+-------------+----------------+--------+
| Id | Parent | Description | NumberOfThings | Number |
+----+--------+-------------+----------------+--------+
| A | NULL | This is A | 1 | NULL |
| B | NULL | This is B | 3 | NULL |
| C | NULL | This is C | 2 | NULL |
+----+--------+-------------+----------------+--------+


and you want to use the
NumberOfThings
to X create children number of things:

+-----+--------+-------------+----------------+--------+
| Id | Parent | Description | NumberOfThings | Number |
+-----+--------+-------------+----------------+--------+
| A | NULL | This is A | 1 | NULL |
| B | NULL | This is B | 3 | NULL |
| C | NULL | This is C | 2 | NULL |
| A-1 | A | This is A | 1 | 1 |
| B-1 | B | This is B | 1 | 1 |
| C-1 | C | This is C | 1 | 1 |
| B-2 | B | This is B | 1 | 2 |
| C-2 | C | This is C | 1 | 2 |
| B-3 | B | This is B | 1 | 3 |
+-----+--------+-------------+----------------+--------+


How would I go about doing this? I have an idea that I could use a LEFT JOIN and perhaps some kind of wildcard ON clause but I am not sure where to begin with this.

This is using SQL Server 2012, but I believe there can be a solution that isnt specific to any DB engine.

SQL Fiddle with first attempt: http://sqlfiddle.com/#!6/dcafc/2

Answer

It's a bit messy, but a recursive CTE can do this:

with cte as (
  select Id as BaseId,
         cast(Id as varchar(10)) as Id,
         cast(null as varchar(1)) as Parent,
         Description,
         NumberOfThings,
         NumberOfThings as TotalCount,
         1 as CurrentCount,
         null as Number
    from NumThings
   union all
  select c.BaseId,
         cast(c.BaseId + '-' + cast(c.CurrentCount as varchar(8)) as varchar(10)) as Id,
         c.BaseId as Parent,
         c.Description,
         1 as NumberOfThings,
         c.TotalCount,
         c.CurrentCount + 1 as CurrentCount,
         c.CurrentCount as Number
    from cte c
   where c.CurrentCount <= c.TotalCount
)
select Id,
       Parent,
       Description,
       NumberOfThings,
       Number
  from cte
 order by case when Parent is null then 0 else 1 end,
          Number,
          Id;

Demo.