locked
How to repeat rows based on column value RRS feed

  • Question

  • Hi all,

    I have a query that gives me a result with a column value for example 4.

    I now want to repeat this row 4 times with a new column that calculated from 1 - 4.

    Or when column value is 3 I want to repeat row 3 times with new column name 1-3

    Can anyone help me with this please? :)


    Thursday, April 30, 2015 12:38 PM

Answers

  • Hi Yvanlathem,

    To achieve your requirement, you can reference the recursive approach as below.

    DECLARE @T TABLE(Col1 CHAR(1),Col2 INT) INSERT INTO @T VALUES('A',1),('B',2),('C',3)

    ;WITH Cte([Char],[Repeat]) AS ( SELECT Col1,Col2 FROM @T UNION ALL SELECT [Char],[Repeat]-1 FROM Cte WHERE [Repeat]>1 ) SELECT * FROM Cte ORDER BY [Char],[Repeat] OPTION(MAXRECURSION 0) /* Output Char Repeat A 1 B 1 B 2 C 1 C 2 C 3 */


    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support


    • Edited by Eric__Zhang Friday, May 1, 2015 6:56 AM
    • Proposed as answer by Eric__Zhang Thursday, May 7, 2015 8:47 AM
    • Marked as answer by Eric__Zhang Monday, May 11, 2015 2:14 AM
    Friday, May 1, 2015 6:55 AM
  • Here is how you can do it

    CREATE TABLE TestTable
    (
     ID INT IDENTITY(1,1),
     Col1 varchar(10),
     Repeats INT
    )
    
    INSERT INTO TESTTABLE
    VALUES ('A',2), ('B',4),('C',1),('D',0)
    
    WITH x AS 
    (
      SELECT TOP (SELECT MAX(Repeats)+1 FROM TestTable) rn = ROW_NUMBER() 
      OVER (ORDER BY [object_id]) 
      FROM sys.all_columns 
      ORDER BY [object_id]
    )
    SELECT * FROM x
    CROSS JOIN TestTable AS d
    WHERE x.rn <= d.Repeats 
    ORDER BY Col1;

    SQL Fiddle Example

    Output:


    Glad to help! Please remember to accept the answer if you found it helpful. It will be useful for future readers having same issue.


    My Profile on Microsoft ASP.NET forum


    Thursday, April 30, 2015 1:02 PM
  • CREATE TABLE test(Col varchar(20), val int)
    
    INSERT INTO test VALUES ('aaa',4), ('bbb',3),('ccc',1) 
    select Col,num from test cross apply(values (1),(2),(3),(4),(5),(6),(7),(8),(9)) d(num)
    where num<=val
    
    drop TABLE test
     

    • Marked as answer by Eric__Zhang Monday, May 11, 2015 2:14 AM
    Friday, May 1, 2015 1:19 PM

All replies

  • You can use a Tall table for this.

    SELECT 
    	mt.*,
    	ROW_NUMBER() OVER (PARTITION BY mt.ID ORDER BY t.n) AS GroupRow
    FROM 
    	dbo.MyTable mt
    	JOIN Tally t
    		ON t.n <= mt.NumColumn


    Jason Long

    Thursday, April 30, 2015 12:59 PM
  • Here is how you can do it

    CREATE TABLE TestTable
    (
     ID INT IDENTITY(1,1),
     Col1 varchar(10),
     Repeats INT
    )
    
    INSERT INTO TESTTABLE
    VALUES ('A',2), ('B',4),('C',1),('D',0)
    
    WITH x AS 
    (
      SELECT TOP (SELECT MAX(Repeats)+1 FROM TestTable) rn = ROW_NUMBER() 
      OVER (ORDER BY [object_id]) 
      FROM sys.all_columns 
      ORDER BY [object_id]
    )
    SELECT * FROM x
    CROSS JOIN TestTable AS d
    WHERE x.rn <= d.Repeats 
    ORDER BY Col1;

    SQL Fiddle Example

    Output:


    Glad to help! Please remember to accept the answer if you found it helpful. It will be useful for future readers having same issue.


    My Profile on Microsoft ASP.NET forum


    Thursday, April 30, 2015 1:02 PM
  • Hi Yvanlathem,

    To achieve your requirement, you can reference the recursive approach as below.

    DECLARE @T TABLE(Col1 CHAR(1),Col2 INT) INSERT INTO @T VALUES('A',1),('B',2),('C',3)

    ;WITH Cte([Char],[Repeat]) AS ( SELECT Col1,Col2 FROM @T UNION ALL SELECT [Char],[Repeat]-1 FROM Cte WHERE [Repeat]>1 ) SELECT * FROM Cte ORDER BY [Char],[Repeat] OPTION(MAXRECURSION 0) /* Output Char Repeat A 1 B 1 B 2 C 1 C 2 C 3 */


    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support


    • Edited by Eric__Zhang Friday, May 1, 2015 6:56 AM
    • Proposed as answer by Eric__Zhang Thursday, May 7, 2015 8:47 AM
    • Marked as answer by Eric__Zhang Monday, May 11, 2015 2:14 AM
    Friday, May 1, 2015 6:55 AM
  • CREATE TABLE test(Col varchar(20), val int)
    
    INSERT INTO test VALUES ('aaa',4), ('bbb',3),('ccc',1) 
    select Col,num from test cross apply(values (1),(2),(3),(4),(5),(6),(7),(8),(9)) d(num)
    where num<=val
    
    drop TABLE test
     

    • Marked as answer by Eric__Zhang Monday, May 11, 2015 2:14 AM
    Friday, May 1, 2015 1:19 PM
  • why would anyone want to destroy 1NF like this?

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, May 1, 2015 8:35 PM
  • You can try this solution also using the master.dbo.spt_values

    create table MyTable (QTY int, ITEM varchar(10))
    insert into MyTable Values
    (1, 'aaa'),
    (2, 'bbb'),
    (4, 'ccc')
    
    SELECT  t1.*, t2.number + 1 RepeatNumber
    FROM    MyTable t1
    JOIN    master.dbo.spt_values t2 ON t2.type = 'P' AND t2.number < t1.QTY
    Live Demo
    Friday, February 21, 2020 5:39 PM