Repeating rows based on the frequency

Question

I have a table with 2 columns named A and B which is defined as :

A   B
c1  2
c2  3
c3  4

The expected output is :

The question doesn't appear to include any attempt at all to solve the problem. StackOverflow expects you to [try to solve your own problem first](https://meta.stackoverflow.com/questions/261592/how-much-research-effort-is-expected-of-stack-overflow-users), as your attempts help us to better understand what you want. Please edit the question to show what you've tried, and show a specific roadblock you're running into with [Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve). For more information, please see [How to Ask](https://stackoverflow.com/help/how-to-ask). — Andreas, Mar 28 '19 at 07:30
This is unclear. But if it were clear it would be a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. — philipxy, Mar 28 '19 at 07:33

score 1 · Answer 1 · answered Mar 28 '19 at 07:33

CREATE TABLE #table2
    ([A] varchar(2), [B] int)
;

INSERT INTO #table2
    ([A], [B])
VALUES
    ('c1', 2),
    ('c2', 3),
    ('c3', 4)

;WITH nums AS
       (SELECT 1 AS value ,a,b from #table2
        UNION ALL
        SELECT value + 1  AS value  ,A,b
        FROM nums
        WHERE nums.value <B)
    SELECT a,value
    FROM nums order by a,value

output

a   value
c1  1
c1  2
c2  1
c2  2
c2  3
c3  1
c3  2
c3  3
c3  4

score 0 · Answer 2 · answered Mar 28 '19 at 07:46

Created a Table valued function in which i used Recursive cte to evaluate recurring value of given in put Then Join the table with function using Cross Apply

CREATE FUNCTION [dbo].[udf_GetData] (
    @Data INT
    )
RETURNS @output TABLE (
    Data INT

    )

BEGIN       
        ;WITH CTe
        AS
        (
            SELECT 1 As Data 
            UNION ALL
            SELECT Data+1
            FROM CTe
            WHERE Data < @Data
        )
        INSERT INTO @output
        SELECT Data FROM CTe

    RETURN
END

Sample Data and explained How to call function using CROSS APPLY

 DECLARE @Data AS TABLE (A VARCHAR(10),B INT)
    INSERT INTO @Data

    SELECT 'c1',  2 UNION ALL
    SELECT 'c2',  3 UNION ALL
    SELECT 'c3',  4

    SELECT d.A,
    (SELECT [dbo].[udf_GetData](d.B)) AS RecData
    FROM @Data d

Result

A   RecursiveData
----------------
c1      1
c1      2
c2      1
c2      2
c2      3
c3      1
c3      2
c3      3
c3      4

score 0 · Answer 3 · answered Mar 28 '19 at 07:53

You could try this:

// test data
declare @tbl table(A char(2),  B int);
insert into @tbl values
('c1',  2),
('c2',  3),
('c3',  4);
// create CTE with numbers which we will need to join
declare @max int;
select @max = max(B) from @tbl;
;with numbers as (
    select 1 n
    union all
    select n + 1 from numbers
    where n < @max
)
// join numbers with your table
select A, n from @tbl t
join numbers n on t.B >= n.n 
order by A, n

score 0 · Answer 4 · answered Mar 28 '19 at 08:31

0

Say, your table name is test.

WITH r(a, b, repeat) as 
(SELECT a, b, 1 from test
union all
select a, b, repeat+1 from  r
where r.repeat < r.b)
select * from r
ORDER BY a, repeat;

answered Mar 28 '19 at 08:31

riyaB

307
1
3
21

Repeating rows based on the frequency

4 Answers4