March 21, 2013 at 9:42 am
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
March 21, 2013 at 10:04 am
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
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
March 21, 2013 at 10:25 am
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.
March 21, 2013 at 10:27 am
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
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
March 21, 2013 at 10:40 am
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'.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy