SQL Explode Lines

If you have a select sql statement that returns the following rows

INVCode  DESC   Qty    Price
123      K6-300    2    50.00
124      CD40      3    20.00

How can I transform this with a SQL statement to:

INVCode  DESC      Price
123      K6-300    50.00
123      K6-300    50.00
124      CD40      20.00
124      CD40      20.00
124      CD40      20.00

IOW I need to make multiple rows depending on the count.
If this is harder than it seems I'll up the points.
davidpmAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RRRCommented:
Hi, davidpm.
You should use this solution:
1)Create some auxilary table with 3 fields: INVCode, DESC, Price.
2)By using recordset that opened to Qty field fill this aux table.
If you want I can paste the code.

Good Luck
RRR.
0
RRRCommented:
Here some funtion:

Public Function MultiplyFields()
Dim db As Database, rsSource As Recordset, rsDestin As Recordset
Dim tbSource As TableDef, tdDestin As TableDef
Dim i As Long

    Set db = CurrentDb
    Set tbSource = db.TableDefs("MyTable")
    Set rsSource = tbSource.OpenRecordset
   
    Set tdDestin = db.TableDefs("AuxTable")
    Set rsDestin = tdDestin.OpenRecordset
    While Not rsDestin.EOF
        For i = 1 To rsSource!Qty
            rsDestin!INVCode = rsSource!INVCode
            rsDestin!DESC = rsSource!DESC
            rsDestin!Price = rsSource!Price
        Next
    Wend
   
    Set rsSource = Nothing
    Set rsDestin = Nothing
    Set tbSource = Nothing
    Set tdDestin = Nothing
    Set db = Nothing
   

End Function


Good Luck
RRR.
0
RRRCommented:
Sorry , some corrections:
Public Function MultiplyFields()
Dim db As Database, rsSource As Recordset, rsDestin As Recordset
Dim tbSource As TableDef, tdDestin As TableDef
Dim i As Long

    Set db = CurrentDb
    Set tbSource = db.TableDefs("MyTable")
    Set rsSource = tbSource.OpenRecordset
   
    Set tdDestin = db.TableDefs("AuxTable")
    Set rsDestin = tdDestin.OpenRecordset
    rsSource.MoveFirst
    While Not rsSource.EOF
        rsDestin.AddNew
        For i = 1 To rsSource!Qty
            rsDestin!INVCode = rsSource!INVCode
            rsDestin!DESC = rsSource!DESC
            rsDestin!Price = rsSource!Price
        Next
        rsDestin.Update
        rsSource.MoveNext
    Wend
   
    Set rsSource = Nothing
    Set rsDestin = Nothing
    Set tbSource = Nothing
    Set tdDestin = Nothing
    Set db = Nothing
   

End Function

RRR.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

davidpmAuthor Commented:
Yes walking the fields with code is an option. I suspect that the addnew and update have to be inside the for loop though?
I wonder if this can be done with SQL?
0
BrianWrenCommented:
I don't think that that can be done with SQL...

And you are right about the AddNew/UpDate.

    With rsSource
    While Not .EOF
        For i = 1 To !Qty
            rsDestin.AddNew
            rsDestin!INVCode = !INVCode
            rsDestin!DESC = !DESC
            rsDestin!Price = !Price
            rsDestin.Update
            .MoveNext
        Next
    Wend
    End With

Brian
0
davidpmAuthor Commented:
O my. So close. I believe the move next has to be out of the for loop.

I was playing with an insert sql clause followed by an update that reduces qty by one. Still looping but more sql.

0
RRRCommented:
OK. Anything else?? :))
After first pasted function I pasted new one updated with addnew and update.

davidpm: Did you tried it???

RRR.
0
davidpmAuthor Commented:
It appears as if what I really wanted to do is not possible so I am giving up the points to rrr as I used some of his code.
Thank you all
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.