Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert duplicate rows based on number in the column Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 9:42 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 23, 2014 6:37 AM
Points: 49, Visits: 116
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
Post #1433874
Posted Thursday, March 21, 2013 10:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 7,105, Visits: 13,455
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 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
Exploring Recursive CTEs by Example Dwain Camps
Post #1433886
Posted Thursday, March 21, 2013 10:25 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 23, 2014 6:37 AM
Points: 49, Visits: 116
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.
Post #1433897
Posted Thursday, March 21, 2013 10:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 7,105, Visits: 13,455
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 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
Exploring Recursive CTEs by Example Dwain Camps
Post #1433898
Posted Thursday, March 21, 2013 10:40 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:01 PM
Points: 22,980, Visits: 31,456
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'.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1433905
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse