CODEWITHSUNDEEP
sql-serversql-server-2008
user3650554
user3650554

Reputation: 41

query that will duplicate records based on quantity

I am using SQL Server 2008 and looking for a query that will duplicate records based on quantity

Table has QTY and PartNumber Columns. Need to add an additional record for each QTY. So say a part number has a qty of 3. I need three rows with that partnumber.

Any Help would be greatly appreciated. Thank You.

Upvotes: 3

Views: 1639

Answers (3)

Richard Body
Richard Body

Reputation: 1

I wanted to do the same, and to make the function more useful, I created a table function based on the code example:

CREATE FUNCTION dbo.tfn_all_ints(@max_val integer) 
RETURNS @retTable TABLE 
 (
  val INT 
 )
BEGIN
/* returns @max_val rows with Vals 1 to @max_val */
/* limited to 99 because of the recursion limit  */
WITH CTE (Vals) 
AS (
    SELECT 1 
    UNION ALL
    SELECT 1 + Vals 
    FROM CTE WHERE Vals < 99
   )

INSERT INTO @retTable(val)
SELECT Vals
FROM CTE C 
WHERE Vals <= @max_val

RETURN
END

call example: SELECT * FROM tfn_all_ints(2)

Upvotes: 0

Serpiton
Serpiton

Reputation: 3684

If you have quantities over your MAXRECURSION you can switch to a different number generation subquery

With B (N) AS (
  SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
  SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
  SELECT 8 UNION ALL SELECT 9
), C (N) AS (
  SELECT b.N + t.N*10 + h.N*100
  FROM   b 
         CROSS JOIN b t
         CROSS JOIN b h
)
SELECT A.PartNumber
     , 1 AS QTY
     , A.Category
     , A.[Description]
     , A.Size
     , A.Tags
     , A.Sheet
     , A.Room
FROM   Test_Table A 
       INNER JOIN C ON C.n <= A.QTY
ORDER BY A.PartNumber

If your base table have lots of rows a better approach will be to create a table, temporary or permanent, to store the result of the number generator and then join that table to the base one.

WITH B (N) AS (
  SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
  SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
  SELECT 8 UNION ALL SELECT 9
)
SELECT b.N + t.N*10 N
INTO   #number
FROM   b 
       CROSS JOIN b t
       CROSS JOIN b h
GO

SELECT A.PartNumber
     , 1 AS QTY
     , A.Category
     , A.[Description]
     , A.Size
     , A.Tags
     , A.Sheet
     , A.Room
FROM   Test_Table A 
       INNER JOIN #number ON #number.N <= A.QTY
ORDER BY A.PartNumber

Upvotes: 1

M.Ali
M.Ali

Reputation: 69524

Test Data

CREATE TABLE Test_Table
(
   PartNumber       VARCHAR(20) , 
   Quantity         INT, 
   [Description]    VARCHAR(20), 
   Category         VARCHAR(20),  
   Size             VARCHAR(20), 
   Tags             VARCHAR(20)
)
GO
INSERT INTO Test_Table 
VALUES
('Part 100', 3, 'Description 100', 'Cat100', 'Size100', 'Tag100'),
('Part 101', 2,'Description 101', 'Cat101', 'Size101', 'Tag101')
GO

Query

;WITH CTE (Vals) 
AS (
    SELECT 1 

     UNION ALL

    SELECT 1 + Vals 
    FROM CTE WHERE Vals < 99
   )
SELECT A.PartNumber
      , 1 AS QTY
      ,A.Category
      ,A.[Description]
      ,A.Size
      ,A.Tags
FROM       Test_Table A 
INNER JOIN CTE C   ON C.Vals <= A.Quantity
ORDER BY A.PartNumber

Result Set

╔════════════╦═════╦══════════╦═════════════════╦═════════╦════════╗
║ PartNumber ║ QTY ║ Category ║   Description   ║  Size   ║  Tags  ║
╠════════════╬═════╬══════════╬═════════════════╬═════════╬════════╣
║ Part 100   ║   1 ║ Cat100   ║ Description 100 ║ Size100 ║ Tag100 ║
║ Part 100   ║   1 ║ Cat100   ║ Description 100 ║ Size100 ║ Tag100 ║
║ Part 100   ║   1 ║ Cat100   ║ Description 100 ║ Size100 ║ Tag100 ║
║ Part 101   ║   1 ║ Cat101   ║ Description 101 ║ Size101 ║ Tag101 ║
║ Part 101   ║   1 ║ Cat101   ║ Description 101 ║ Size101 ║ Tag101 ║
╚════════════╩═════╩══════════╩═════════════════╩═════════╩════════╝

Upvotes: 1

Related Questions