Solved

SQL Query question on generating extra records using MS SQL

Posted on 2013-12-19
3
336 Views
Last Modified: 2013-12-20
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
Comment
Question by:johnj_01201
3 Comments
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 250 total points
ID: 39730576
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
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 39731638
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
 
LVL 1

Author Closing Comment

by:johnj_01201
ID: 39732602
Thank You!!!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question