Add multiple rows in a SQL SELECT query

Question

I have a table and I want to write a query which will give duplicate rows of that table based on the value of column quantity in that table.

Suppose the table is one given below

 name | quantity
------|----------
  a   |  1
  b   |  1
  c   |  3
  d   |  2
  e   |  1

And I want to be able to write a query in T-SQL so that it would give a result which would look like follows;

 name | number | quantity
------|--------|----------
  a   |  1     |    1
  b   |  1     |    1
  c   |  1     |    3
  c   |  2     |    3
  c   |  3     |    3
  d   |  1     |    2
  d   |  2     |    2
  e   |  1     |    1

So in this result, there are 3 rows for "c" as its quantity is 3 and the number increments as the line appears for nth time.

I have found this question which has been answered and accepted, but I don't quite understand how to apply it in to my scenario. Any help on this is much appreciated..!

Take a look at technet.microsoft.com/en-us/library/ms189798(v=sql.110).aspx — Kamran Shahid
– Kamran Shahid, Commented Sep 11, 2013 at 16:47

Charles Bretana · Accepted Answer · 2013-09-11 17:47:29Z

5

try this:

 With Ints(n) As
 (Select 1 Union All
  Select n + 1 From Ints
  Where n < 1000)    
 Select t.Name, i.n 
 from myTable t join Ints i 
   on i.n <= t.Quantity
 option(MaxRecursion 1000)

edited Sep 11, 2013 at 17:47

answered Sep 11, 2013 at 16:30

Charles Bretana

147k24 gold badges158 silver badges219 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2013-09-11 16:28:29Z

2

Create a numbers table and do a join:

with numbers as (
      select 1 as n
      union all
      select 1 + n
      from numbers
      where 1 + n <= 50
    )
select t.name, numbers.n, t.quantity
from t join
     numbers
     on t.quantity <= numbers.n;

This assumes the maximum quantity is 50. You could put in select max(quantity) from t) if you wanted it more flexible.

If quantity can be big, you might want to add OPTION (MAXRECURSION 0).

answered Sep 11, 2013 at 16:28

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Ben Lin · Accepted Answer · 2013-09-11 16:32:28Z

0

I don't have sql server now. I think DENSE_RANK is the solution.

answered Sep 11, 2013 at 16:32

Ben Lin

84310 silver badges15 bronze badges

Comments

Bogdan Sahlean · Accepted Answer · 2013-09-11 16:48:45Z

First, I would create a table with numbers thus:

CREATE TABLE dbo.Number(Num INT IDENTITY(1,1) PRIMARY KEY);
GO
INSERT INTO dbo.Number
DEFAULT VALUES; -- this will insert 10000 rows into dbo.Number table (all numbers between 1 and 10000)
GO 10000

Then, I would create a constraint just to be sure that quantity column cann't contains values greater than 10000:

ALTER TABLE dbo.MyTable
ADD CONSTRAINT CK_MyTable_Quantity CHECK(Quantity BETWEEN 1 AND 10000);

And the solution:

SELECT t.*, ROW_NUMBER() OVER(PARTITION BY t.name ORDER BY @@SPID) AS number
FROM dbo.MyTable t INNER JOIN dbo.Number n ON t.Quantity <= n.Num;

Note #1: This solution assumes that Quantity column is mandatory (NOT NULL) and has an integer data type ([TINY|SMALL|BIG]INT, NUMERIC|DECIMAL(p,0)).

Note #2: This solution assumes that minimum QTY is 1 and maximum QTY is 10000.

Collectives™ on Stack Overflow

Add multiple rows in a SQL SELECT query

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related