Solved

SQL Query question on generating extra records using MS SQL

Posted on 2013-12-19
3
337 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 60

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

739 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