Closed. This question needs details or clarity. It is not currently accepting answers.
Off the top of my head:
WITH cte AS (
SELECT *, 1 AS level FROM yourTable UNION ALL
SELECT *, 2 FROM yourTable UNION ALL
SELECT *, 3 FROM yourTable UNION ALL
SELECT *, 4 FROM yourTable
-- add more queries for higher levels
PIECES >= level
In SQL Server or MySQL 8+ we could use a recursive CTE instead of the hard-coded version above, to handle an arbitrary number of record duplications.
Proceedings 2003 VLDB Conference: 29th International Conference on , These queries select records from the the Wisconsin Benchmark's  TENK1 relation and accesses a variable number of attributes from each selected record. Query 5.4 Experiment 2: Attribute Scale-up We now examine the performance If the record you are working with is assigned to a variable, each time you access that variable, you’ll access the same record instance. However, if instead you access a record multiple times by invoking an expression that retrieves it from an external source (e.g. a database or web service), each retrieval may technically return a different
SQL Server. Assuming
WITH VTE AS(
1 AS Piece
FROM VTE V
FROM VTE V
JOIN rCTE r ON V.ID = r.ID
AND V.Pieces > r.Piece)
ORDER BY ID, Piece;
WITH VTE AS(
SELECT TOP (SELECT MAX(Pieces) FROM VTE)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
FROM N N1, N N2) --100 rows, Add more as needed
FROM VTE V
JOIN Tally T ON V.Pieces >= T.I
ORDER BY V.ID,
As mentioned in the comment, I recommend the tally, especially if
PIECES will have much larger values.
How to set a result from a SOQL query to a variable?, What do I need to get to assign the result of this query to a variable? Total_Creatures__c" is not the record that caused this trigger to fire. Now the only issue I have is it adds up the "Number in Deck" field accross all decks. Once the last record has been accessed, the RecordCount property indicates the total number of undeleted records in the Recordset or TableDef object. To force the last record to be accessed, use the MoveLast method on the Recordset object. You can also use an SQL Count function to determine the approximate number of records your query will return.
Create a function
CREATE FUNCTION NumbersTable (
RETURNS @NumbersTable TABLE (i int)
WITH CTE_NumbersTable AS (
SELECT @fromNumber AS i
SELECT i + @byStep
(i + @byStep) <= @toNumber
INSERT INTO @NumbersTable
SELECT i FROM CTE_NumbersTable OPTION (MAXRECURSION 0)
, CrossApply create table #tab(Id int,PRODUCT varchar(200), PIECES int);
insert into #tab VALUES(1,'BANANA',2),
create table #tab2(Id int,PRODUCT varchar(200), PIECES int);
INSERT INTO #tab2(Id, PRODUCT,PIECES)
SELECT Id, PRODUCT,PIECES
FROM #tab t1
CROSS APPLY dbo.NumbersTable(1,t1.PIECES,1)
select * from #tab2
Oracle 11g: PL/SQL Programming, To do this, you need to declare the table of records variable in a package At times, applications need to have variables that persist across executing a number of As you see in Chapter 4, this same issue comes up when querying data via Count total records in a query and assign that number to a variable I'd like to find a total record count of following query and assign the record count to a variable. Thanks.
Advances in Databases and Information Systems: Associated , The query selected a constant number of data records from every DW version. up: the number of DW versions NDWV=100; the number of fact data records in every DW 5.1 Variable Number of DW Versions This experiment evaluated the I am working on an SQL query which should return data having same or different number of columns for each row For example, I have the following data which comes from a CTE as follows With CTE As( select distinct b.Acc_Num, a.Type ,a.START ,a.END ,a.USAGE from USAGE_DETAIL a join Table2 b on b.CUSTOMER_TKN=a.CUSTOMER_TKN ) Select * from CTE Using PL/SQL Collections and Records, Varrays (short for variable-size arrays) hold a fixed number of elements (although to and from the database server is to set up data values in associative arrays, then entries for each row of the main table, and access it through join queries. The SQL ROW_NUMBER Function assigns the row number or rank number to each record present in a partition. In this Ranking function example, we show how to rank the partitioned records present in a SQL Server table. The following SQL row number query will partition the data by Occupation and assign the row number using the yearly income.
6 Performing SQL Operations from PL/SQL, Querying Data with PL/SQL: Explicit Cursor FOR Loops The following example shows how to generate a new sequence number and refer to that same and refer to those identifiers from other rows to set up parent-child relationships. When you select or fetch a physical rowid into a UROWID variable, you can use the This really helps! However, the only input I have got is a Record variable. I would not be able to query on any of the tables to form a cursor. But I know the table on which this record is based as it is declared using ROWTYPE. So I've got only one record (row) with many fields to print. – Iban Jan 3 '12 at 19:39
Comments Why are you SHOUTING what you want at us? If you want something, what's stopping you? What have you tried to solve the problem and why didn't it work? If you haven't made an attempt, why not? What research did you perform, and what about it did you not understand or fail to implement? Please don't make demands of the volunteers here to do it for you. Are you using MySQL or SQL Server? They are not the same thing. Note that SQL is not a very good data generation tool. It might be easier to do this outside of your database. "How can i?" In SQL Server, I would use a Tally. the numbers are going to remain low (i.e. 10 or less in my opinion) an rCTE would also be a candidate, but the performance will dip quickly once you get to larger values for If
Thank you for your answer,but max pieces is changeable, can I make this query dynamic? Or how can i ? First, tell us which database and version you are using. To extend my answer on either database, you could just add more queries to union with higher levels. See the answer by @larnu for a recursive CTE option on SQL Server. Microsoft SQL Server Management Studio 17 That isn't a database @YunusEmreDECECI, it's an application. You can get the version with
Thank you ,it's solve my problem. You're welcome @YunusEmreDEVECİ, please do accept the answer as the solution if so. You get reputation points for it (really important as a new Contributer) and future users know the answer was helpful. Thanks. It's okey,i am new here Why use a multi-line table-value function here? They are vastly slower than inline TVFs.