June 16, 2017 at 5:44 am
Hi everyone 🙂
I'm having this issue and any help will be really appreciated.
I'm simplifying the example:
This is the table:
ProductID Units
001 4
002 1
003 2
And what I need to get is something like this:
ProductID Number Units
001 1 4
001 2 4
001 3 4
001 4 4
002 1 1
003 1 2
003 2 2
Can anyone help me with this?
Thanks a lot.
June 16, 2017 at 6:05 am
carlos.rincon - Friday, June 16, 2017 5:44 AMHi everyone 🙂
I'm having this issue and any help will be really appreciated.
I'm simplifying the example:
This is the table:
ProductID Units
001 4
002 1
003 2
And what I need to get is something like this:
ProductID Number Units
001 1 4
001 2 4
001 3 4
001 4 4
002 1 1
003 1 2
003 2 2Can anyone help me with this?
Thanks a lot.
The first step is to create a tally function or table. Jeff Moden has an excellent article on the topic at http://www.sqlservercentral.com/articles/T-SQL/62867/. If you're not familiar with them, it's worth it to take the time to read the article. It'll change the way you look at data. Here's Itzik Ben-Gan's zero-read function.
CREATE FUNCTION dbo.TallyN(@N Bigint) RETURNS TABLE WITH SCHEMABINDING
AS
--Credit: This function was written by Itzik Ben-Gan at http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers
RETURN WITH level0 AS (
SELECT 0 AS g UNION ALL SELECT 0), --2
level1 AS (SELECT 0 AS g FROM level0 AS a CROSS JOIN level0 AS b), --2^2 = 4
level2 AS (SELECT 0 AS g FROM level1 AS a CROSS JOIN level1 AS b), --4^2 = 16
level3 AS (SELECT 0 AS g FROM level2 AS a CROSS JOIN level2 AS b), --16^2 = 256
level4 AS (SELECT 0 AS g FROM level3 AS a CROSS JOIN level3 AS b), --256^2 = 65536
level5 AS (SELECT 0 AS g FROM level4 AS a CROSS JOIN level4 AS b), --65536^2 = 4294967296
Tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM level5)
SELECT TOP (@N) N
FROM Tally
ORDER BY N;
From there, querying the results you want is pretty simple:
IF OBJECT_ID('tempdb.dbo.#test', 'u') IS NOT NULL DROP TABLE #test;
CREATE TABLE #test (
ProductID Char(3),
Units Integer);
INSERT INTO #test(ProductID, Units)
VALUES('001', 4),
('002', 1),
('003', 2);
SELECT d.ProductID, RN = t.N, d.Units
FROM #test d
CROSS APPLY util.dbo.TallyN(d.Units) t
ORDER BY d.ProductID, t.N;
June 16, 2017 at 6:30 am
Thanks a lot, I'll give it a try.
June 20, 2017 at 1:20 pm
carlos.rincon - Friday, June 16, 2017 5:44 AMHi everyone 🙂
I'm having this issue and any help will be really appreciated.
I'm simplifying the example:
This is the table:
ProductID Units
001 4
002 1
003 2
And what I need to get is something like this:
ProductID Number Units
001 1 4
001 2 4
001 3 4
001 4 4
002 1 1
003 1 2
003 2 2Can anyone help me with this?
Thanks a lot.
I doubt a function with is necessary you are just wanting the ROW_NUMBER() function call as your number generating column.
just add the following code as another column in your SELECT statement
ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ProductID) as Number
June 20, 2017 at 5:37 pm
Smendle - Tuesday, June 20, 2017 1:20 PMcarlos.rincon - Friday, June 16, 2017 5:44 AMHi everyone 🙂
I'm having this issue and any help will be really appreciated.
I'm simplifying the example:
This is the table:
ProductID Units
001 4
002 1
003 2
And what I need to get is something like this:
ProductID Number Units
001 1 4
001 2 4
001 3 4
001 4 4
002 1 1
003 1 2
003 2 2Can anyone help me with this?
Thanks a lot.I doubt a function with is necessary you are just wanting the ROW_NUMBER() function call as your number generating column.
just add the following code as another column in your SELECT statementROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ProductID) as Number
The OP is asking to have additional rows created based on the value in the Units column. ROW_NUMBER() isn't able to do that at all.
Ed's solution is going to be the best option for this type of request.
June 20, 2017 at 10:08 pm
Jason A. Long - Tuesday, June 20, 2017 5:37 PMSmendle - Tuesday, June 20, 2017 1:20 PMcarlos.rincon - Friday, June 16, 2017 5:44 AMHi everyone 🙂
I'm having this issue and any help will be really appreciated.
I'm simplifying the example:
This is the table:
ProductID Units
001 4
002 1
003 2
And what I need to get is something like this:
ProductID Number Units
001 1 4
001 2 4
001 3 4
001 4 4
002 1 1
003 1 2
003 2 2Can anyone help me with this?
Thanks a lot.I doubt a function with is necessary you are just wanting the ROW_NUMBER() function call as your number generating column.
just add the following code as another column in your SELECT statementROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ProductID) as Number
The OP is asking to have additional rows created based on the value in the Units column. ROW_NUMBER() isn't able to do that at all.
Ed's solution is going to be the best option for this type of request.
Open mouth insert foot.
However that fancy function really isnt neccessary. A recursive CTE should do the trick just fine.
declare @Products table(productID char(3), Units INT)
INSERT INTO @Products(productID,Units) values ('001',4),('002',1),('003',2)
;With RecursiveCTE
as (
select
productID,
Units,
num=1
from @Products
union all
select
l.productID,
l.units,
num=num+1
from RecursiveCTE l
join @Products d on l.ProductID = d.ProductID
where
l.num < d.units
)
select ProductID,num,Units from RecursiveCTE
Order by ProductID,num
Results
ProductID num Units
001 1 4
001 2 4
001 3 4
001 4 4
002 1 1
003 1 2
003 2 2
June 20, 2017 at 10:38 pm
Smendle - Tuesday, June 20, 2017 10:08 PMOpen mouth insert foot.However that fancy function really isnt neccessary. A recursive CTE should do the trick just fine.
declare @Products table(productID char(3), Units INT)
INSERT INTO @Products(productID,Units) values ('001',4),('002',1),('003',2);With RecursiveCTE
as (
select
productID,
Units,
num=1
from @Products
union all
select
l.productID,
l.units,
num=num+1
from RecursiveCTE l
join @Products d on l.ProductID = d.ProductID
where
l.num < d.units
)
select ProductID,num,Units from RecursiveCTE
Order by ProductID,numResults
ProductID num Units
001 1 4
001 2 4
001 3 4
001 4 4
002 1 1
003 1 2
003 2 2
I didn't say the method Ed chose was the only option... I said it was the BEST option... and it is.
The "Ben-Gan" style tally table is orders of magnitude more efficient than using a recursive CTE.
You won't see the difference with only 3 rows, generating only a few new rows each, but try it with !M+ rows... You'll see a massive difference.
June 20, 2017 at 11:04 pm
Here's a quick & dirty test to demonstrate the difference (and show that you can get similar performance w/o having to create an actual function).
declare @Products table(productID char(3), Units INT);
INSERT INTO @Products(productID,Units) values ('001',40000),('002',100000),('003',200000),('004',200000),('005',200000);
DECLARE
@b1 DATETIME2(7),
@b2 DATETIME2(7)
;
SET @b1 = SYSDATETIME();
;With RecursiveCTE
as (
select
productID,
Units,
num=1
from @Products
union all
select
l.productID,
l.units,
num=num+1
from RecursiveCTE l
join @Products d on l.ProductID = d.ProductID
where
l.num < d.units
)
select ProductID,num,Units
INTO #temp1
FROM RecursiveCTE
Order by ProductID,num
OPTION(MAXRECURSION 0);
--DROP TABLE #temp1
SET @b2 = SYSDATETIME();
SELECT
p.productID, p.Units, tally.rn
INTO #temp2
FROM
@Products p
CROSS APPLY (
SELECT TOP (p.Units)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
( VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ) n1 (n)
CROSS APPLY ( VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ) n2 (n)
CROSS APPLY ( VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ) n3 (n)
CROSS APPLY ( VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ) n4 (n)
CROSS APPLY ( VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ) n5 (n)
CROSS APPLY ( VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ) n6 (n)
) tally (rn);
--DROP TABLE #temp2;
SELECT
Recursion = DATEDIFF(ms, @b1, @b2),
BenGanTally = DATEDIFF(ms, @b2, SYSDATETIME())
;
Results are in milliseconds...
Recursion BenGanTally
----------- -----------
10812 313
June 20, 2017 at 11:05 pm
Jason A. Long - Tuesday, June 20, 2017 10:38 PMSmendle - Tuesday, June 20, 2017 10:08 PMOpen mouth insert foot.However that fancy function really isnt neccessary. A recursive CTE should do the trick just fine.
declare @Products table(productID char(3), Units INT)
INSERT INTO @Products(productID,Units) values ('001',4),('002',1),('003',2);With RecursiveCTE
as (
select
productID,
Units,
num=1
from @Products
union all
select
l.productID,
l.units,
num=num+1
from RecursiveCTE l
join @Products d on l.ProductID = d.ProductID
where
l.num < d.units
)
select ProductID,num,Units from RecursiveCTE
Order by ProductID,numResults
ProductID num Units
001 1 4
001 2 4
001 3 4
001 4 4
002 1 1
003 1 2
003 2 2I didn't say the method Ed chose was the only option... I said it was the BEST option... and it is.
The "Ben-Gan" style tally table is orders of magnitude more efficient than using a recursive CTE.
You won't see the difference with only 3 rows, generating only a few new rows each, but try it with !M+ rows... You'll see a massive difference.
I guess I should not have typed the words "not needed", again my bad.
Im not trying to be glib or pithy here, as you say its just another option. I only offered it because I didnt originally post a useful comment so I was just trying to make amends for that.
June 20, 2017 at 11:42 pm
Smendle - Tuesday, June 20, 2017 11:05 PMI guess I should not have typed the words "not needed", again my bad.
Im not trying to be glib or pithy here, as you say its just another option. I only offered it because I didnt originally post a useful comment so I was just trying to make amends for that.
No worries. I've seen quite a few people using recursion to do this sort of thing, including some fairly well known names in the SQL community...
I was going to post a couple links about but Ed already has them in his signature... (see the the first two links)... Plus the link to Itzik's article in Ed's function comments.
Hopefully, one day, Microsoft will see fit to implement Erland Sommarskog's request to build this functionality directly into the product... Add a built-in table of numbers (feel free to give it an up vote :D)
June 21, 2017 at 1:11 am
It's funny how this things evolve from a question to an academycal debate. I love this due to I can learn a lot from you. I truly appreciate all of your comments. I'll step aside to read them and learn from you, people, that obviously know a lot more than me about this matter.
June 21, 2017 at 5:23 am
Jason A. Long - Tuesday, June 20, 2017 11:42 PMSmendle - Tuesday, June 20, 2017 11:05 PMI guess I should not have typed the words "not needed", again my bad.
Im not trying to be glib or pithy here, as you say its just another option. I only offered it because I didnt originally post a useful comment so I was just trying to make amends for that.No worries. I've seen quite a few people using recursion to do this sort of thing, including some fairly well known names in the SQL community...
I was going to post a couple links about but Ed already has them in his signature... (see the the first two links)... Plus the link to Itzik's article in Ed's function comments.
Hopefully, one day, Microsoft will see fit to implement Erland Sommarskog's request to build this functionality directly into the product... Add a built-in table of numbers (feel free to give it an up vote :D)
Thanks, Jason. That particular MS Connect item celebrated its 10th birthday earlier this year and is still active. It would be nice to have a function that ran at machine language speeds, but I wouldn't hold my breath on it.
Smendle, you're absolutely correct in that there are plenty of other approaches to the problem. Jeff Moden has a great article on rCTEs and performance at http://www.sqlservercentral.com/articles/T-SQL/74118/.
June 21, 2017 at 5:31 am
carlos.rincon - Wednesday, June 21, 2017 1:11 AMIt's funny how this things evolve from a question to an academycal debate. I love this due to I can learn a lot from you. I truly appreciate all of your comments. I'll step aside to read them and learn from you, people, that obviously know a lot more than me about this matter.
Welcome to SSC. I think this is the best SQL Server online community in the world for that very reason. There are lots of times where a relatively simple question evolves into a discussion of different approaches and tweaks to make them more performant. There are some really smart people on this site that bring not only a breadth of knowledge, but great depth. For me, the discussion is where the real epiphany and learning occurs. Enjoy.
June 21, 2017 at 8:15 am
Ed Wagner - Wednesday, June 21, 2017 5:23 AMThanks, Jason. That particular MS Connect item celebrated its 10th birthday earlier this year and is still active. It would be nice to have a function that ran at machine language speeds, but I wouldn't hold my breath on it.Smendle, you're absolutely correct in that there are plenty of other approaches to the problem. Jeff Moden has a great article on rCTEs and performance at http://www.sqlservercentral.com/articles/T-SQL/74118/.
Yea, there are a few connect items that MS really needs to implement
#1... Inline scalar functions: The Scalar Expression function would speed performance while keeping the benefits of functions & User defined function performance is unacceptable
#2... GREATEST & LEAST functions: Can't seem to find the Connect page at the moment.
#3... ALTER TABLE syntax for changing column order (yea I know... set theory and all... sql server doesn't care about column order but human developers do)
June 21, 2017 at 8:59 am
Jason A. Long - Wednesday, June 21, 2017 8:15 AMEd Wagner - Wednesday, June 21, 2017 5:23 AMThanks, Jason. That particular MS Connect item celebrated its 10th birthday earlier this year and is still active. It would be nice to have a function that ran at machine language speeds, but I wouldn't hold my breath on it.Smendle, you're absolutely correct in that there are plenty of other approaches to the problem. Jeff Moden has a great article on rCTEs and performance at http://www.sqlservercentral.com/articles/T-SQL/74118/.
Yea, there are a few connect items that MS really needs to implement
#1... Inline scalar functions: The Scalar Expression function would speed performance while keeping the benefits of functions & User defined function performance is unacceptable
#2... GREATEST & LEAST functions: Can't seem to find the Connect page at the moment.
#3... ALTER TABLE syntax for changing column order (yea I know... set theory and all... sql server doesn't care about column order but human developers do)
Thanks, Jason. I didn't know about #1. I just voted for it to bring the vote up to 601 Yes to 3 No. I see it's also celebrated its 10th birthday. There were some SQL 2008 Books Online pages that referenced Inline Scalar Functions and Itzik Ben-Gan wrote an article about them years ago. The concept certainly isn't new, but there's no separate implementation of it.
Between how new the Connect item is and the closeness of the vote, MS might not want to rush into anything. 😉
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply