Insert duplicate rows based on number in the column

Question

Post reply

Insert duplicate rows based on number in the column

knakka99

Say Hey Kid

Points: 671
More actions
March 21, 2013 at 9:42 am

#394446

First table (@OrigTab) has Parts and quantity, now we need copy each part record basing on quanity show in @DesireTable
Declare @OrigTab Table(Part varchar(30), qty int)
insert into @OrigTab
select 'X51','3' union
select 'A5','1' union
select '123','2' union
select '054','5'
select * from @OrigTab ORDER BY 1 DESC
/*Basing on the number of quantity duplicate rows need to be created with a new id column(tag)in the below format */
Declare @DesireTable TABLE(TAG VARCHAR(13),Part varchar(30), qty int)
insert into @DesireTable
select 'PNM-01','X51','3' union
select 'PNM-02','X51','3' union
select 'PNM-03','X51','3' union
select 'PNM-04','A5','1' union
select 'PNM-05','123','2' union
select 'PNM-06','123','2' union
select 'PNM-07','054','4'union
select 'PNM-08','054','4'union
select 'PNM-09','054','4'union
select 'PNM-10','054','4'union
select 'PNM-11','054','4'
select * from @DesireTable

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Declare @OrigTab Table(Part varchar(30), qty int)

insert into @OrigTab

select 'X51','3' union

select 'A5','1' union

select '123','2' union

select '054','5'

select * from @OrigTab ORDER BY 1 DESC

;WITH

-- Make an inline tally table (CTE)

E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

), --10E+1 or 10 rows

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

Tally(N) AS (SELECT 1 FROM E2 a, E2 b)

-- use the tally table as a source of rows

SELECT

TAG = 'PNM-'+RIGHT('0'+CAST(ROW_NUMBER() OVER(ORDER BY Part DESC) AS VARCHAR(2)),2),

o.Part,

o.qty

FROM @OrigTab o

CROSS APPLY (SELECT TOP(o.qty) N FROM Tally) x

ORDER BY Part DESC

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

knakka99 Say Hey Kid Points: 671 More actions · Answer 2

Hi ChrisM@Work thank you for the reply.. I have one question in case they are more than 100 quanties what do I need to do.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

knakka 14475 (3/21/2013)
Hi ChrisM@Work thank you for the reply.. I have one question in case they are more than 100 quanties what do I need to do.

;WITH

-- Make an inline tally table (CTE)

E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

), --10E+1 or 10 rows

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

Tally(N) AS (SELECT 1 FROM E2 a, E2 b)

SELECT COUNT(*) FROM Tally

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

knakka 14475 (3/21/2013)
Hi ChrisM@Work thank you for the reply.. I have one question in case they are more than 100 quanties what do I need to do.

Adjust this piece of code to append more zeros.

TAG = 'PNM-'+RIGHT('0'+CAST(ROW_NUMBER() OVER(ORDER BY Part DESC) AS VARCHAR(2)),2)

For instance:

TAG = 'PNM-'+RIGHT('00000000'+CAST(ROW_NUMBER() OVER(ORDER BY Part DESC) AS VARCHAR(2)),3)

will provide '001' through '999'.