Adding extra rows in a select result set
in [Programming SQL]
|
From: gv on 11 Aug 2010 09:11 Hello, I would like to generate extra rows based on a value from a table column without using union? I'm stump here..... --Rules: -- when partnumber = ty67892 add 2 extra rows in results -- when partnumber = nb67j3e add 4 extra rows in results -- when partnumber = sdx5x78 add 7 extra rows in reults --and then all should have a unique column ID with extra rows. DECLARE @EXTRAROWS TABLE (ExtraRowID INT IDENTITY(1,1) NOT NULL,PartName VARCHAR(100)NOT NULL,PartNumber VARCHAR(50)NOT NULL) INSERT INTO @EXTRAROWS (PartName,PartNumber) VALUES ('Lower Flex Joint','K1258U'), ('Upper Flex Spacer','ty67892'), ('Cross T lad','45gh'), ('Joint Y Fence','89023T'), ('H etch plac','rty29cv'), ('A sim Cross Bar','nb67j3e'), ('O ring disc','sdx5x78') ;WITH NewResults AS (SELECT ROW_NUMBER() OVER (ORDER BY ER.PARTNUMBER) AS 'Row Number', ER.PartName,ER.PartNumber FROM @EXTRAROWS ER GROUP BY ER.PartName,ER.PartNumber ) SELECT [Row Number],PartName,PartNumber FROM NewResults thanks, gv
From: Sylvain Lafontaine on 11 Aug 2010 11:14 Probably that you could use a Left Join with a second table having the constant values 1..8 and add the proper conditions in the ON part of the left join. -- Sylvain Lafontaine, ing. MVP - Access Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "gv" <viator.gerry(a)gmail.com> wrote in message news:O248raVOLHA.604(a)TK2MSFTNGP05.phx.gbl... > Hello, > > I would like to generate extra rows based on a value from a table column > without using union? > I'm stump here..... > > --Rules: > > -- when partnumber = ty67892 add 2 extra rows in results > -- when partnumber = nb67j3e add 4 extra rows in results > -- when partnumber = sdx5x78 add 7 extra rows in reults > > --and then all should have a unique column ID with extra rows. > > DECLARE @EXTRAROWS TABLE > (ExtraRowID INT IDENTITY(1,1) NOT NULL,PartName VARCHAR(100)NOT > NULL,PartNumber VARCHAR(50)NOT NULL) > INSERT INTO @EXTRAROWS > (PartName,PartNumber) > VALUES ('Lower Flex Joint','K1258U'), > ('Upper Flex Spacer','ty67892'), > ('Cross T lad','45gh'), > ('Joint Y Fence','89023T'), > ('H etch plac','rty29cv'), > ('A sim Cross Bar','nb67j3e'), > ('O ring disc','sdx5x78') > > ;WITH NewResults AS > (SELECT > ROW_NUMBER() OVER > (ORDER BY ER.PARTNUMBER) AS 'Row Number', > ER.PartName,ER.PartNumber > FROM @EXTRAROWS ER > GROUP BY ER.PartName,ER.PartNumber ) > > SELECT [Row Number],PartName,PartNumber > FROM NewResults > > thanks, > gv > > > >
From: gv on 11 Aug 2010 11:34 Please show me an example on the part you are able to add the extra rows? I have it working this way using cross joins but, what if the extra rows I need generated are like 1000? --Rules: -- when partnumber = ty67892 add 2 extra rows in results -- when partnumber = nb67j3e add 4 extra rows in results -- when partnumber = sdx5x78 add 7 extra rows in reults DECLARE @EXTRAROWS TABLE (ExtraRowID INT IDENTITY(1,1) NOT NULL,PartName VARCHAR(100)NOT NULL,PartNumber VARCHAR(50)NOT NULL) INSERT INTO @EXTRAROWS (PartName,PartNumber) VALUES ('Lower Flex Joint','K1258U'), ('Upper Flex Spacer','ty67892'), ('Cross T lad','45gh'), ('Joint Y Fence','89023T'), ('H etch plac','rty29cv'), ('A sim Cross Bar','nb67j3e'), ('O ring disc','sdx5x78') ;WITH NewResults AS (SELECT ROW_NUMBER() OVER (ORDER BY ER.PARTNUMBER) AS 'RowID', ER.PartName,ER.PartNumber FROM @EXTRAROWS ER GROUP BY ER.PartName,ER.PartNumber ) SELECT ROW_NUMBER() OVER (ORDER BY NR.ROWID) AS NewrowID, NR.PartName,NR.PartNumber FROM NewResults NR LEFT JOIN (SELECT A.* FROM NewResults A CROSS JOIN(SELECT '1' AS I UNION SELECT '2' ) AS B WHERE A.PartNumber = 'ty67892') TWO ON NR.ROWID = TWO.ROWID LEFT JOIN (SELECT A.* FROM NewResults A CROSS JOIN(SELECT '1' AS I UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' ) AS B WHERE A.PartNumber = 'nb67j3e') Four ON NR.ROWID = Four.ROWID LEFT JOIN (SELECT A.* FROM NewResults A CROSS JOIN(SELECT '1' AS I UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' UNION SELECT '7' ) AS B WHERE A.PartNumber = 'sdx5x78') SEVEN ON NR.ROWID = SEVEN.ROWID Thanks gv "Sylvain Lafontaine" <sylvainlafontaine2009(a)yahoo.ca> wrote in message news:OsaEzfWOLHA.3732(a)TK2MSFTNGP02.phx.gbl... > Probably that you could use a Left Join with a second table having the > constant values 1..8 and add the proper conditions in the ON part of the > left join. > > -- > Sylvain Lafontaine, ing. > MVP - Access > Blog/web site: http://coding-paparazzi.sylvainlafontaine.com > Independent consultant and remote programming for Access and SQL-Server > (French) > > > "gv" <viator.gerry(a)gmail.com> wrote in message > news:O248raVOLHA.604(a)TK2MSFTNGP05.phx.gbl... >> Hello, >> >> I would like to generate extra rows based on a value from a table column >> without using union? >> I'm stump here..... >> >> --Rules: >> >> -- when partnumber = ty67892 add 2 extra rows in results >> -- when partnumber = nb67j3e add 4 extra rows in results >> -- when partnumber = sdx5x78 add 7 extra rows in reults >> >> --and then all should have a unique column ID with extra rows. >> >> DECLARE @EXTRAROWS TABLE >> (ExtraRowID INT IDENTITY(1,1) NOT NULL,PartName VARCHAR(100)NOT >> NULL,PartNumber VARCHAR(50)NOT NULL) >> INSERT INTO @EXTRAROWS >> (PartName,PartNumber) >> VALUES ('Lower Flex Joint','K1258U'), >> ('Upper Flex Spacer','ty67892'), >> ('Cross T lad','45gh'), >> ('Joint Y Fence','89023T'), >> ('H etch plac','rty29cv'), >> ('A sim Cross Bar','nb67j3e'), >> ('O ring disc','sdx5x78') >> >> ;WITH NewResults AS >> (SELECT >> ROW_NUMBER() OVER >> (ORDER BY ER.PARTNUMBER) AS 'Row Number', >> ER.PartName,ER.PartNumber >> FROM @EXTRAROWS ER >> GROUP BY ER.PartName,ER.PartNumber ) >> >> SELECT [Row Number],PartName,PartNumber >> FROM NewResults >> >> thanks, >> gv >> >> >> >> > >
From: Sylvain Lafontaine on 11 Aug 2010 12:03 Hi, first, I'm sorry to have said a Left Join instead of an ordinary Join. I don't know what I was thinking. In your case, when I meant to put the condition inside the ON, I was talking about something like (untested) : SELECT * FROM @EXTRAROWS ER JOIN (Select 0 as I 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 ) AS B On ( B.I = 0 or (ER.ParNumber = 'ty67892' and B.I between 1 and 2) or (ER.ParNumber = 'nb67j3e' and B.I between 1 and 4) or (ER.ParNumber = 'sdx5x78' and B.I between 1 and 7) ) .... Instead of building dynamically the second table using a set of Union All (slightly faster than simply using an UNION), you could also use a real table. This will deal perfectly with your case where you must add 1000 new rows. -- Sylvain Lafontaine, ing. MVP - Access Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "gv" <viator.gerry(a)gmail.com> wrote in message news:elx7CrWOLHA.4424(a)TK2MSFTNGP04.phx.gbl... > Please show me an example on the part you are able to add the extra rows? > > I have it working this way using cross joins but, what if the extra rows I > need generated are like 1000? > > > --Rules: > > -- when partnumber = ty67892 add 2 extra rows in results > -- when partnumber = nb67j3e add 4 extra rows in results > -- when partnumber = sdx5x78 add 7 extra rows in reults > > DECLARE @EXTRAROWS TABLE > (ExtraRowID INT IDENTITY(1,1) NOT NULL,PartName VARCHAR(100)NOT > NULL,PartNumber VARCHAR(50)NOT NULL) > INSERT INTO @EXTRAROWS > (PartName,PartNumber) > VALUES ('Lower Flex Joint','K1258U'), > ('Upper Flex Spacer','ty67892'), > ('Cross T lad','45gh'), > ('Joint Y Fence','89023T'), > ('H etch plac','rty29cv'), > ('A sim Cross Bar','nb67j3e'), > ('O ring disc','sdx5x78') > > ;WITH NewResults AS > (SELECT > ROW_NUMBER() OVER > (ORDER BY ER.PARTNUMBER) AS 'RowID', > ER.PartName,ER.PartNumber > FROM @EXTRAROWS ER > > GROUP BY ER.PartName,ER.PartNumber ) > > SELECT > ROW_NUMBER() OVER (ORDER BY NR.ROWID) AS NewrowID, > NR.PartName,NR.PartNumber > FROM NewResults NR > LEFT JOIN (SELECT A.* FROM NewResults A > CROSS JOIN(SELECT '1' AS I UNION SELECT '2' ) AS B > WHERE A.PartNumber = 'ty67892') TWO > ON NR.ROWID = TWO.ROWID > LEFT JOIN (SELECT A.* FROM NewResults A > CROSS JOIN(SELECT '1' AS I UNION SELECT '2' UNION SELECT '3' UNION > SELECT '4' ) AS B > WHERE A.PartNumber = 'nb67j3e') Four > ON NR.ROWID = Four.ROWID > LEFT JOIN (SELECT A.* FROM NewResults A > CROSS JOIN(SELECT '1' AS I UNION SELECT '2' UNION SELECT '3' UNION > SELECT '4' > UNION SELECT '5' UNION SELECT '6' UNION SELECT '7' ) AS B > WHERE A.PartNumber = 'sdx5x78') SEVEN > ON NR.ROWID = SEVEN.ROWID > > Thanks > gv > > > "Sylvain Lafontaine" <sylvainlafontaine2009(a)yahoo.ca> wrote in message > news:OsaEzfWOLHA.3732(a)TK2MSFTNGP02.phx.gbl... >> Probably that you could use a Left Join with a second table having the >> constant values 1..8 and add the proper conditions in the ON part of the >> left join. >> >> -- >> Sylvain Lafontaine, ing. >> MVP - Access >> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com >> Independent consultant and remote programming for Access and SQL-Server >> (French) >> >> >> "gv" <viator.gerry(a)gmail.com> wrote in message >> news:O248raVOLHA.604(a)TK2MSFTNGP05.phx.gbl... >>> Hello, >>> >>> I would like to generate extra rows based on a value from a table column >>> without using union? >>> I'm stump here..... >>> >>> --Rules: >>> >>> -- when partnumber = ty67892 add 2 extra rows in results >>> -- when partnumber = nb67j3e add 4 extra rows in results >>> -- when partnumber = sdx5x78 add 7 extra rows in reults >>> >>> --and then all should have a unique column ID with extra rows. >>> >>> DECLARE @EXTRAROWS TABLE >>> (ExtraRowID INT IDENTITY(1,1) NOT NULL,PartName VARCHAR(100)NOT >>> NULL,PartNumber VARCHAR(50)NOT NULL) >>> INSERT INTO @EXTRAROWS >>> (PartName,PartNumber) >>> VALUES ('Lower Flex Joint','K1258U'), >>> ('Upper Flex Spacer','ty67892'), >>> ('Cross T lad','45gh'), >>> ('Joint Y Fence','89023T'), >>> ('H etch plac','rty29cv'), >>> ('A sim Cross Bar','nb67j3e'), >>> ('O ring disc','sdx5x78') >>> >>> ;WITH NewResults AS >>> (SELECT >>> ROW_NUMBER() OVER >>> (ORDER BY ER.PARTNUMBER) AS 'Row Number', >>> ER.PartName,ER.PartNumber >>> FROM @EXTRAROWS ER >>> GROUP BY ER.PartName,ER.PartNumber ) >>> >>> SELECT [Row Number],PartName,PartNumber >>> FROM NewResults >>> >>> thanks, >>> gv >>> >>> >>> >>> >> >> > >
From: gv on 11 Aug 2010 12:59 Thanks You!! gv "Sylvain Lafontaine" <sylvainlafontaine2009(a)yahoo.ca> wrote in message news:eNz%23K7WOLHA.6100(a)TK2MSFTNGP05.phx.gbl... > Hi, first, I'm sorry to have said a Left Join instead of an ordinary > Join. I don't know what I was thinking. In your case, when I meant to put > the condition inside the ON, I was talking about something like (untested) > : > > SELECT * > FROM @EXTRAROWS ER > > JOIN (Select 0 as I 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 ) AS > B > > On ( > B.I = 0 > or (ER.ParNumber = 'ty67892' and B.I between 1 and 2) > or (ER.ParNumber = 'nb67j3e' and B.I between 1 and 4) > or (ER.ParNumber = 'sdx5x78' and B.I between 1 and 7) > ) > ... > > Instead of building dynamically the second table using a set of Union All > (slightly faster than simply using an UNION), you could also use a real > table. This will deal perfectly with your case where you must add 1000 > new rows. > > -- > Sylvain Lafontaine, ing. > MVP - Access > Blog/web site: http://coding-paparazzi.sylvainlafontaine.com > Independent consultant and remote programming for Access and SQL-Server > (French) > > > "gv" <viator.gerry(a)gmail.com> wrote in message > news:elx7CrWOLHA.4424(a)TK2MSFTNGP04.phx.gbl... >> Please show me an example on the part you are able to add the extra rows? >> >> I have it working this way using cross joins but, what if the extra rows >> I need generated are like 1000? >> >> >> --Rules: >> >> -- when partnumber = ty67892 add 2 extra rows in results >> -- when partnumber = nb67j3e add 4 extra rows in results >> -- when partnumber = sdx5x78 add 7 extra rows in reults >> >> DECLARE @EXTRAROWS TABLE >> (ExtraRowID INT IDENTITY(1,1) NOT NULL,PartName VARCHAR(100)NOT >> NULL,PartNumber VARCHAR(50)NOT NULL) >> INSERT INTO @EXTRAROWS >> (PartName,PartNumber) >> VALUES ('Lower Flex Joint','K1258U'), >> ('Upper Flex Spacer','ty67892'), >> ('Cross T lad','45gh'), >> ('Joint Y Fence','89023T'), >> ('H etch plac','rty29cv'), >> ('A sim Cross Bar','nb67j3e'), >> ('O ring disc','sdx5x78') >> >> ;WITH NewResults AS >> (SELECT >> ROW_NUMBER() OVER >> (ORDER BY ER.PARTNUMBER) AS 'RowID', >> ER.PartName,ER.PartNumber >> FROM @EXTRAROWS ER >> >> GROUP BY ER.PartName,ER.PartNumber ) >> >> SELECT >> ROW_NUMBER() OVER (ORDER BY NR.ROWID) AS NewrowID, >> NR.PartName,NR.PartNumber >> FROM NewResults NR >> LEFT JOIN (SELECT A.* FROM NewResults A >> CROSS JOIN(SELECT '1' AS I UNION SELECT '2' ) AS B >> WHERE A.PartNumber = 'ty67892') TWO >> ON NR.ROWID = TWO.ROWID >> LEFT JOIN (SELECT A.* FROM NewResults A >> CROSS JOIN(SELECT '1' AS I UNION SELECT '2' UNION SELECT '3' UNION >> SELECT '4' ) AS B >> WHERE A.PartNumber = 'nb67j3e') Four >> ON NR.ROWID = Four.ROWID >> LEFT JOIN (SELECT A.* FROM NewResults A >> CROSS JOIN(SELECT '1' AS I UNION SELECT '2' UNION SELECT '3' UNION >> SELECT '4' >> UNION SELECT '5' UNION SELECT '6' UNION SELECT '7' ) AS B >> WHERE A.PartNumber = 'sdx5x78') SEVEN >> ON NR.ROWID = SEVEN.ROWID >> >> Thanks >> gv >> >> >> "Sylvain Lafontaine" <sylvainlafontaine2009(a)yahoo.ca> wrote in message >> news:OsaEzfWOLHA.3732(a)TK2MSFTNGP02.phx.gbl... >>> Probably that you could use a Left Join with a second table having the >>> constant values 1..8 and add the proper conditions in the ON part of the >>> left join. >>> >>> -- >>> Sylvain Lafontaine, ing. >>> MVP - Access >>> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com >>> Independent consultant and remote programming for Access and SQL-Server >>> (French) >>> >>> >>> "gv" <viator.gerry(a)gmail.com> wrote in message >>> news:O248raVOLHA.604(a)TK2MSFTNGP05.phx.gbl... >>>> Hello, >>>> >>>> I would like to generate extra rows based on a value from a table >>>> column without using union? >>>> I'm stump here..... >>>> >>>> --Rules: >>>> >>>> -- when partnumber = ty67892 add 2 extra rows in results >>>> -- when partnumber = nb67j3e add 4 extra rows in results >>>> -- when partnumber = sdx5x78 add 7 extra rows in reults >>>> >>>> --and then all should have a unique column ID with extra rows. >>>> >>>> DECLARE @EXTRAROWS TABLE >>>> (ExtraRowID INT IDENTITY(1,1) NOT NULL,PartName VARCHAR(100)NOT >>>> NULL,PartNumber VARCHAR(50)NOT NULL) >>>> INSERT INTO @EXTRAROWS >>>> (PartName,PartNumber) >>>> VALUES ('Lower Flex Joint','K1258U'), >>>> ('Upper Flex Spacer','ty67892'), >>>> ('Cross T lad','45gh'), >>>> ('Joint Y Fence','89023T'), >>>> ('H etch plac','rty29cv'), >>>> ('A sim Cross Bar','nb67j3e'), >>>> ('O ring disc','sdx5x78') >>>> >>>> ;WITH NewResults AS >>>> (SELECT >>>> ROW_NUMBER() OVER >>>> (ORDER BY ER.PARTNUMBER) AS 'Row Number', >>>> ER.PartName,ER.PartNumber >>>> FROM @EXTRAROWS ER >>>> GROUP BY ER.PartName,ER.PartNumber ) >>>> >>>> SELECT [Row Number],PartName,PartNumber >>>> FROM NewResults >>>> >>>> thanks, >>>> gv >>>> >>>> >>>> >>>> >>> >>> >> >> > >
|
Pages: 1 Prev: CREATE BLANK ROWS BETWEEN DATA Next: how to dateadd with bigint |