?
Solved

SQL Query question on generating extra records using MS SQL

Posted on 2013-12-19
3
Medium Priority
?
340 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 1000 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 1000 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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

752 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