October 3, 2016 at 1:23 pm
Hello,
How can i return rows in a table by a value in a column of that table:
for example:
i have a row with table structure below :
!D Schedule ISCI rotationnumber
1 100 abc 9
2 200 cod 5
3 450 xay 1
i woould like to return the row with rotationnumber '9' , to be duplicated 9 times in the same table rotationnumber : 5 , duplicated 5 times in the same table etc...and where we have 1, it should ignore?
does this make sense?
I learn from the footprints of giants......
October 3, 2016 at 1:28 pm
JALLY (10/3/2016)
Hello,How can i return rows in a table by a value in a column of that table:
for example:
i have a row with table structure below :
!D Schedule ISCI rotationnumber
1 100 abc 9
2 200 cod 5
3 450 xay 1
i woould like to return the row with rotationnumber '9' , to be duplicated 9 times in the same table rotationnumber : 5 , duplicated 5 times in the same table etc...and where we have 1, it should ignore?
does this make sense?
I think so. May I ask why you would want to create these duplicates?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 3, 2016 at 1:40 pm
Since someone else recently asked that very same question, it sounds like homework.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 3, 2016 at 1:49 pm
yes, i want to create these duplicates.
I learn from the footprints of giants......
October 3, 2016 at 1:56 pm
JALLY (10/3/2016)
yes, i want to create these duplicates.
Merely repeating your request, rather than answering my question, does not fill me with enthusiasm in terms of providing a useful response, sorry.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 3, 2016 at 2:12 pm
i apologise for i did not see the 'why' you asked ,
i would like to create the duplicates to fulfill a criteria in a broader context of a stored procedure, eg. the procedure rotates and assigns a code based on the rotationNumber, now i want it to rotate the same code based on percentage assigned as rotation percentage. if rotation percentage is 90%, this would mean it would rotate the code 9 times, this is what i would like to duplicate as 9 rows.
does this make sense?
I learn from the footprints of giants......
October 3, 2016 at 2:20 pm
Hope you get an A....
declare @input table (ID int, Schedule int, ISCI varchar(10), rotationnumber int)
insert into @input
values
(1, 100, 'abc', 9),
(2, 200, 'cod', 5),
(3, 450, 'xay', 1)
select N.*
from @input N
cross apply (SELECT top (rotationnumber) N from Vtally) dt
where rotationnumber > 1
Be careful not to stand where a giant is about to leave a footprint.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 5, 2016 at 8:49 am
This is a situation where a function will actually out perform a permanent tally table...
The function...
CREATE FUNCTION dbo.tfn_Tally
(
@RowCount INT
)
RETURNS TABLE WITH SCHEMABINDING
RETURN
WITH
n (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n))
SELECT TOP (@RowCount)
n= ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM n n1, n n2, n n3, n n4, n n5, n n6;-- Maxes out at 1M rows...
GO
The test... (note: dbo.Tally is an indexed 100k row, single column tally table)
DECLARE
@b1 DATETIME2(7),
@b2 DATETIME2(7);
-------------------------------------------
SET @b1 = SYSDATETIME();
SELECT
COUNT(1)
FROM
dbo.Tally t
CROSS APPLY dbo.tfn_Tally(t.N) fn
WHERE
t.N <= 10000;
-------------------------------------------
-------------------------------------------
SET @b2 = SYSDATETIME();
SELECT
COUNT(1)
FROM
dbo.Tally t
CROSS APPLY (SELECT TOP(t.N) t2.N FROM dbo.Tally t2) tp
WHERE
t.N <= 10000;
-------------------------------------------
SELECT
Query_1 = DATEDIFF(ms,@b1,@b2),
Query_2 = DATEDIFF(ms,@b2,SYSDATETIME());
The Results...
Query_1 Query_2
----------- -----------
1304 6046
Stats & time:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row(s) affected)
Table 'Tally'. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1312 ms, elapsed time = 1304 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row(s) affected)
Table 'Tally'. Scan count 2, logical reads 83041, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 4938 ms, elapsed time = 6045 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply