CODEWITHSUNDEEP
sqlsql-servert-sql
procma
procma

Reputation: 1450

SQL multiplying rows in select

I would like to select some rows multiple-times, depending on the column's value.

Source table

Article | Count
===============
A       | 1
B       | 4
C       | 2

Wanted result

Article
===============
A
B
B
B
B
C
C

Any hints or samples, please?

Upvotes: 0

Views: 213

Answers (5)

Praveen ND
Praveen ND

Reputation: 560

Can obtain the output using simple WHILE LOOP

      DECLARE @table TABLE 
        (ID int ,Article varchar(5),[Count] int)

        INSERT INTO @table
        (ID,Article,Count)
        VALUES
        (1,'A',1),(2,'B',4),(3,'C',2)

        DECLARE @temp TABLE 
        (Article varchar(5))

        DECLARE @Cnt1 INT
        DECLARE @Cnt2 INT
        DECLARE @Check INT
        DECLARE @max INT
        SET @max =0
        SET @Cnt1 = (SELECT Count(Article) FROM @table)

        WHILE (@max < @Cnt1)
        BEGIN
            SET @max = @max +1
            SET @Cnt2 =  (SELECT  [Count] FROM @table WHERE ID =@max)
            SET @Check =(SELECT [Count] FROM @table WHERE ID =@max)
            WHILE (@Cnt2 > 0)
            BEGIN
                INSERT INTO @temp
                SELECT Article FROM @table WHERE [Count] =@Check
                SET @Cnt2 = @Cnt2 -1
            END   
        END
        SELECT * FROM @temp

Upvotes: 0

xdd
xdd

Reputation: 545

one more CTE

with cte_t as (
    select c as c, 1 as i
        from mytable
        group by c
    union all
    select t.c, ctet.i + 1
        from mytable t
            join cte_t ctet
                on ctet.c = t.c  
                    and ctet.i < t.i
)
select cte_t.c
    from cte_t
    order by cte_t.c

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460288

You could also use a recursive CTE which works with numbers > 10 (here up to 1000):

With NumberSequence( Number ) as
(
    Select 0 as Number
        union all
    Select Number + 1
        from NumberSequence
    where Number BETWEEN 0 AND 1000
)
SELECT Article
FROM ArticleCounts
CROSS APPLY NumberSequence
WHERE Number BETWEEN 1 AND [Count] 
ORDER BY Article
Option (MaxRecursion 0)

Demo

A number-table will certainly be the best option.

http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-2

Upvotes: 1

Eralper
Eralper

Reputation: 6622

Please check following SQL script Before executing the SELECT statement, note that I used a user function which is used to simulate a numbers table You can find the sql codes of numbers table in SQL Server at referred tutorial

----create table myTempTbl (Article varchar(10), Count int)
--insert into myTempTbl select 'A',1
--insert into myTempTbl select 'B',4
--insert into myTempTbl select 'C',2

select t.* 
from myTempTbl t
cross apply dbo.NumbersTable(1,100,1) n
where n.i <= t.Count
order by t.Article

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 176134

You could use:

SELECT m.Article
FROM mytable m
CROSS APPLY (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS s(n)
WHERE s.n <= m.[Count];

LiveDemo

Note: CROSS APLLY with any tally table. Here values up to 10.

Related: What is the best way to create and populate a numbers table?

Upvotes: 4

Related Questions