Duplicate rows using left join

Question

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

You need to JOIN to a Numbers table of some kind, where the join matches every record in the Numbers table <= the `NumberOfThings` column. This will help: http://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table — Joel Coehoorn, Nov 13 '16 at 02:58

score 0 · Answer 1 · answered Nov 13 '16 at 02:26

0

It seems like you are looking for the union of the set plus the cross product of the set with itself. Though, I am not sure this is correct given the desired outcome for the NumberOfThings column.

SELECT
    [Id]
    , [Parent]
    , [NumberOfThings]
FROM
    [dbo].[NumThings]
UNION ALL
SELECT
    A.[Id] + '-' + CAST(B.NumberOfThings AS VARCHAR(50))
    , B.[Id] As Parent
    , A.[NumberOfThings]
FROM
    [dbo].[NumThings] A
    , [dbo].[NumThings] B
GO

answered Nov 13 '16 at 02:26

cstick

374
3
10

That isnt quite what I wanted: http://sqlfiddle.com/#!6/dcafc/2 As you can see it created 3 child A's when it needed just 1 – ParoX Nov 13 '16 at 02:53

Daniel · Answer 2 · 2016-11-13T05:18:23.820

0

Here.....I believe this is the result you are looking for:

     SELECT
         [Id]
          , [Parent]
          , [NumberOfThings]
      FROM
[dbo].[NumThings]
       UNION ALL
     SELECT
          A.[Id] + '-' + CAST(B.NumberOfThings AS VARCHAR(50))
           , A.[Id] As Parent
        , B.[NumberOfThings]
     FROM
         [dbo].[NumThings] A
     LEFT JOIN [dbo].[NumThings] B
         ON B.NumberOfThings BETWEEN 1 AND A.NumberOfThings
   GO

edited Nov 13 '16 at 05:18

answered Nov 13 '16 at 04:38

Daniel

174
5

Nice one. Unfortunately, it won't work if, for example, you change the value of `NumberOfThings` in one of the rows to `10`. – sstan Nov 13 '16 at 04:52
My misunderstanding then as I thought "NumberOfThings" was some related to tge number of "thing" in your table....ie. 10 things would require A-J......if this is not the case then I don't believe cross joining tables really serves a purpose – Daniel Nov 13 '16 at 05:06

score 0 · Accepted Answer · answered Nov 13 '16 at 04:56

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.

Duplicate rows using left join

3 Answers3