July 15, 2018 at 8:23 am
Hello
For calculation purposes I need to make a request of type uinon all according to an input parameter For example if @ n = 1 I do select * from table1
if @n = 2 I do select * from table1 union all select * from table1
if @n = 3 I do select * from table1 union all select * from table1 union all select * from table1
if @n = 4 I do select * from table1 union all select * from table1 union all select * from table1 union all select * from table1
Who can help me please on this need?
Thank you
July 15, 2018 at 11:47 am
joujousagem2006 1602 - Sunday, July 15, 2018 8:23 AMHello
For calculation purposes I need to make a request of type uinon all according to an input parameter For example if @ n = 1 I doselect * from table1
if @n = 2 I doselect * from table1 union all select * from table1
if @n = 3 I doselect * from table1 union all select * from table1 union all select * from table1
if @n = 4 I doselect * from table1 union all select * from table1 union all select * from table1 union all select * from table1
Who can help me please on this need?
Thank you
recursive CTE should be able to help you out in this.
Out of curiosity do you have any practical use of such a requirement?
The below loop will run for twice
with cte(x,n)
as (select x,1 as n
from t
union all
select x,n+1
from cte
where n<2 /*Change this value to the passed in parameter*/
)
select * from cte;
July 15, 2018 at 4:36 pm
joujousagem2006 1602 - Sunday, July 15, 2018 8:23 AMHello
For calculation purposes I need to make a request of type uinon all according to an input parameter For example if @ n = 1 I doselect * from table1
if @n = 2 I doselect * from table1 union all select * from table1
if @n = 3 I doselect * from table1 union all select * from table1 union all select * from table1
if @n = 4 I doselect * from table1 union all select * from table1 union all select * from table1 union all select * from table1
Who can help me please on this need?
Thank you
First of all, we never like to use SELECT * in production code. It looks like what you're trying to do is completely destroying normalization by repeating row after row after row in such a way that this non-table can never have a key. If duplication is meaningful, then you use what Dr. Codd called a degree of duplication in the table. This is a column that has a count of how many times those values appear.
CREATE TABLE Foobar
(degree_dup INTEGER DEFAULT 1 NOT NULL
CHECK (degree_dup > 0),
foo_id CHAR(10) NOT NULL,
PRIMARY KEY (degree_dup, foo_id),
...):
Please post DDL and follow ANSI/ISO standards when asking for help.
July 15, 2018 at 7:51 pm
jcelko212 32090 - Sunday, July 15, 2018 4:36 PMFirst of all, we never like to use SELECT * in production code.
While that's normally sound advice (saying "Return only the columns you need is better advice"), as with all else, "It Depends". SELECT * is actually better performing than naming, say, all 100 columns of a 100 column table when you actually DO need to select all of the columns from the table.. SELECT * is also the preferred method when using WHERE EXISTS or WHERE NOT EXISTS.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 15, 2018 at 8:32 pm
joujousagem2006 1602 - Sunday, July 15, 2018 8:23 AMHello
For calculation purposes I need to make a request of type uinon all according to an input parameter For example if @ n = 1 I doselect * from table1
if @n = 2 I doselect * from table1 union all select * from table1
if @n = 3 I doselect * from table1 union all select * from table1 union all select * from table1
if @n = 4 I doselect * from table1 union all select * from table1 union all select * from table1 union all select * from table1
Who can help me please on this need?
Thank you
A simple CROSS JOIN with a "Tally Table" or similar structure will do this much more effectively.
For example, if you wanted to play this against the sys.objects table, the code would look like this...
DECLARE @DupeFactor INT;
SELECT @DupeFactor = 4;
WITH
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))E0(N))
,E6(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d, E1 e, E1 f)
,Tally(N) AS (SELECT TOP (4) CONVERT(INT,ROW_NUMBER() OVER (ORDER BY N)) FROM E6)
SELECT src.* FROM Tally t CROSS JOIN sys.Objects src
;
To play it against a different table, just replace sys.objects with the correct table name.
To find out more about the "Tally Table", similar structures, and how they can be used to replace forms of RBAR, such as Cursors, While Loops, and recursive CTEs that count, please see the following article for an introduction. Also understand that there's a much more far reaching underlying principle to it all especially when combined with the likes of ROW_NUMBER() that can make you shine in your career. For example, similar principles are used in the nearly instant creation of a million rows of test data and I have to tell you that, when it comes to performance, one proper test is worth a thousand expert opinions. 😉
Here's the link to the article...
http://www.sqlservercentral.com/articles/T-SQL/62867/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply