• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

SQL Query question on generating extra records using MS SQL

How can I do this same thing, but have it generate 1 less extra records and if it's not too much to ask, have the id start with "2" instead of "1" in the extra record field?

Modified example from http://stackoverflow.com/questions/10070816/how-can-sql-create-duplicate-records

;WITH CTE_Data as (
    select id=1, name='Anne',[COUNT]=4
    union select id=2, name='Joe',[COUNT]=6
   
),
CTE_List as (
    select
        id,
        name,
        ind=1
    from CTE_Data
    union all
    select
        l.id,
        l.name,
        ind=ind+1
    from CTE_List l
        join CTE_Data d on l.id = d.id
    where
        l.ind < d.[count]
)
select id,name,ind from CTE_List
order by id,ind

1      Anne      1
1      Anne      2
1      Anne      3
1      Anne      4
2      Joe      1
2      Joe      2
2      Joe      3
2      Joe      4
2      Joe      5
2      Joe      6

I already have ind=1, so it doesn't need to be generated.
I would like the results to be:
1      Anne      1
1      Anne      2
1      Anne      3
2      Joe      1
2      Joe      2
2      Joe      3
2      Joe      4
2      Joe      5

and if possible have it start counting at 2 instead of 1?
1      Anne      2
1      Anne      3
1      Anne      4
2      Joe      2
2      Joe      3
2      Joe      4
2      Joe      5
2      Joe      6

Oh, I don't know what "CTE" means, I just found the example and it worked.
0
johnj_01201
Asked:
johnj_01201
2 Solutions
 
Carl TawnSystems and Integration DeveloperCommented:
Try:
;WITH CTE_Data as (
    select id=1, name='Anne',[COUNT]=3
    union select id=2, name='Joe',[COUNT]=5
    
),
CTE_List as (
    select
        id,
        name,
        ind=2
    from CTE_Data
    union all
    select
        l.id,
        l.name,
        ind=ind+1
    from CTE_List l
        join CTE_Data d on l.id = d.id
    where 
        l.ind < d.[count]
)
select id,name,ind from CTE_List
order by id,ind

Open in new window

P.S. CTE stands for "common table expression", and is basically an in memory table designed to provide better performance that a Temp table when used with small datasets.
0
 
Kevin CrossChief Technology OfficerCommented:
Note: if you can have any counts equal one, you may want to add a filter.

;WITH CTE_Data as (
    select id=1, name='Anne',[COUNT]=3
    union select id=2, name='Joe',[COUNT]=5
    
),
CTE_List as (
    select
        id,
        name,
        ind=2
    from CTE_Data
    where [count] > 1
    union all
    select
        l.id,
        l.name,
        ind=ind+1
    from CTE_List l
        join CTE_Data d on l.id = d.id
    where 
        l.ind < d.[count]
)
select id,name,ind from CTE_List
order by id,ind

Open in new window


*please credit Expert above with solution.  I just wanted to point out the extra WHERE clause.
0
 
johnj_01201Author Commented:
Thank You!!!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now