select row n times with SQL

Question

I have 3 tables. Person, Group and PersonGroup. Each Person can be in multiple Groups. In the PersonGroup table there's a field called ShowCount, which says how many times a Person should be shown.

Example: Person Bill is in group StackOverflow and should be displayed 3 times so the output should be like this

Bill 
Bill 
Bill

is there a way to do this with SQL?

Yes, it's possible. Two ideas: 1) join with a "numbers" table 2) [recursive CTE](http://msdn.microsoft.com/en-us/library/ms186243.aspx). — Mark Byers, Oct 13 '11 at 11:01
Your data model sounds a little odd - why is bill in the same group three times? — Paddy, Oct 13 '11 at 11:06
He's not. if he was there would be three entries. The ShowCount says how many times his address should be printed out (or be on the report). — domueni, Oct 13 '11 at 11:08
See this similar question: http://stackoverflow.com/questions/2472662/make-sql-select-same-row-multiple-times (the answer by @van) — ypercubeᵀᴹ, Oct 13 '11 at 11:21
Or this answer by @Evan: http://stackoverflow.com/questions/988916/postgresql-select-a-single-row-x-amount-of-times — ypercubeᵀᴹ, Oct 13 '11 at 11:23
@MarkByers i tried to make it with recursion. but without success and i did it with the numbers table but i don't like the solution — domueni, Oct 13 '11 at 11:38

score 1 · Answer 1 · answered Oct 13 '11 at 11:23

1

Try this

select P.name
from person P
join PersonGroup pg on pg.person_id=p.id
join master.dbo.spt_values where type='P' and number>0 and number <= pg.ShowCount

answered Oct 13 '11 at 11:23

Sparky

14,967
2
31
45

score 1 · Accepted Answer · answered Oct 13 '11 at 11:26

Hello a numbers table can help you with solution. You can find the function code at http://www.kodyaz.com/articles/sql-server-tsql-cte-numbers-table.aspx

Here is a sample t-sql script

select
    pg.showcount,
    p.name,
    g.groupname
from PersonGroup pg
inner join Person p on p.personid = pg.personid
inner join Groups g on g.groupid = pg.groupid
inner join dbo.NumbersTable(1,12,1) n on pg.showcount >= n.i

I hope this helps,

score 0 · Answer 3 · answered Oct 14 '11 at 06:13

so thats how we did it now:

WITH ShowCounter AS (SELECT 1 AS ShowCount 
            UNION ALL
            SELECT ShowCount + 1
            FROM ShowCounter 
            WHERE (ShowCount + 1 <= 100))
SELECT Person.Name
FROM ShowCounter 
INNER JOIN Person ON ShowCounter.ShowCount <= Person.ShowCount
ORDER BY PersonGroup.PersonGroupID

this works for ShowCount <= 100

select row n times with SQL

3 Answers3

Linked