CODEWITHSUNDEEP
sqlsql-server
Mel Zen
Mel Zen

Reputation: 53

Need to multiply rows based on Number column in SQL

In my case there is a delivery of Product per Area according to Nos of allotment. 

Rowstamp  DocNos  ProductID  SiteCode   SiteDesc      Qty
1         AAAA    1AA        0200       Mckinley Area  2
2         AAAA    1BB        0200       Mckinley Area  3
3         AAAA    1CC        0200       Mckinley Area  1
4         BBBB    1AA        0300       Southwoods     4
5         BBBB    1BB        0300       Southwoods     2
6         BBBB    1CC        0300       Southwoods     2

But now I need a single row for each element. This will be use for tagging of box of item individually. This is the output I want:

Rowstamp  DocNos  ProductID  SiteCode   Description    Qty  
1         AAAA    1AA        0200       Mckinley Area  1
1         AAAA    1AA        0200       Mckinley Area  1
2         AAAA    1BB        0200       Mckinley Area  1
2         AAAA    1BB        0200       Mckinley Area  1
2         AAAA    1BB        0200       Mckinley Area  1
3         AAAA    1CC        0200       Mckinley Area  1
4         BBBB    1AA        0300       Southwoods     1
4         BBBB    1AA        0300       Southwoods     1
4         BBBB    1AA        0300       Southwoods     1
4         BBBB    1AA        0300       Southwoods     1
5         BBBB    1BB        0300       Southwoods     1
5         BBBB    1BB        0300       Southwoods     1
6         BBBB    1CC        0300       Southwoods     1
6         BBBB    1CC        0300       Southwoods     1

Thank You For usual support!

Upvotes: 4

Views: 1173

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

A simple method uses a recursive subquery:

with cte as (
      select Rowstamp, DocNos, ProductID, SiteCode, Description, qty, 1 as num
      from t
      union all
      select Rowstamp, DocNos, ProductID, SiteCode, Description, qty, num + 1
      from cte
      where num <= qty
     )
select Rowstamp, DocNos, ProductID, SiteCode, Description, num as qty
from cte;

Upvotes: 5

level3looper
level3looper

Reputation: 1051

Cursor walk to create the recs:

CREATE TABLE #Tags
(
    [Rowstamp] [int] NOT NULL,
    [DocNos] [nvarchar](50) NULL,
    [ProductID] [nvarchar](50) NULL,
    [SiteCode] [nvarchar](50) NULL,
    [SiteDesc] [nvarchar](50) NULL,
    [Qty] [int] NULL
) ON [PRIMARY]

Declare @RowStamp Int, @DocNos nVarChar(50),@ProductID nVarChar(50), @SiteCode nVarChar(50),@SiteDesc nVarChar(50),@Qty nVarChar(50)

DECLARE record_cursor CURSOR FOR  
  SELECT [Rowstamp], [DocNos], [ProductID], [SiteCode], [SiteDesc], [Qty]
  FROM [tbl_Tags];  

OPEN record_cursor;  

FETCH NEXT FROM record_cursor INTO
@RowStamp, @DocNos,@ProductID, @SiteCode, @SiteDesc, @Qty;  

Declare @vRecs Int
Set @vRecs = 0

WHILE @@FETCH_STATUS = 0  
BEGIN  
    While @vRecs < @Qty
    Begin
     Insert Into #Tags Values(@RowStamp, @DocNos,@ProductID, @SiteCode, @SiteDesc, 1)
     Set @vRecs = @vRecs+1
    End

    Set @vRecs = 0
    FETCH NEXT FROM record_cursor INTO @RowStamp, @DocNos,@ProductID, @SiteCode, @SiteDesc, @Qty;  
END  

Select * From #Tags 

Drop table #Tags
CLOSE record_cursor;  
DEALLOCATE record_cursor;

Upvotes: 0

Ross Presser
Ross Presser

Reputation: 6255

You can join to a number table. Here I use a query on sysobjects from the master database, since it's always there and likely to have lots of rows. If you happen to have qty values that are bigger than the row count in sysobjects, there are other approaches to getting a source of numbers.

SELECT T.RowStamp, T.DocNos, T.ProductID, T.SiteCode, T.Description, T.qty
FROM T
  INNER JOIN (
        SELECT ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY id) AS num
        FROM master.dbo.sysobjects
        ) AS B
    ON B.num <= T.qty

Upvotes: 0

Related Questions