I have a records on a table the is grouped as show below:

can you anyone assist with a tsql query to "ungroup" the records such that a name appears the number of times based on the count as show below:

CodePudding user response：

Use a recursive CTE to unfold them

T-Sql (MS Sql Server)

;with RCTE as (
    select name, 1 as lvl, [count] as cnt
    from yourtable
    
    union all

    select name, lvl 1, cnt
    from RCTE
    where lvl < cnt
)
select name
from RCTE;

Postgresql, SQLite

with RECURSIVE RCTE as (
    select name, 1 as lvl, "count" as cnt
    from yourtable
    
    union all

    select name, lvl 1, cnt
    from RCTE
    where lvl < cnt
)
select name
from RCTE;

Demo on db<>fiddle here

Pl/Sql (Oracle)

with RCTE (name, lvl, cnt) as (
    select name, 1 as lvl, "count" as cnt
    from yourtable
    
    union all

    select name, lvl 1, cnt
    from RCTE
    where lvl < cnt
)
select name
from RCTE;

MySql 8, MariaDB 10.3

with RECURSIVE RCTE as (
    select name, 1 as lvl, `count` as cnt
    from yourtable
    
    union all

    select name, lvl 1, cnt
    from RCTE
    where lvl < cnt
)
select name
from RCTE;

CodePudding user response：

As of Oracle, you could use query that begins at line #6 (because lines #1 - 5 represent sample data).

SQL> with test (name, cnt) as
  2    (select 'Lanre', 2 from dual union all
  3     select 'Jane' , 1 from dual union all
  4     select 'Wale' , 6 from dual
  5    )
  6  select name
  7  from test cross join table(cast(multiset(select level from dual
  8                                           connect by level <= cnt
  9                                          ) as sys.odcinumberlist))
 10  order by name;

NAME
-----
Jane
Lanre
Lanre
Wale
Wale
Wale
Wale
Wale
Wale

9 rows selected.

SQL>