Solved

SQL Query question on generating extra records using MS SQL

Posted on 2013-12-19
3
334 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

809 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