How to combine 2 rows to multiple columns?

350 pts.
Tags:
SQL Query
I have a table with name, plan and amt as columns. Sometimes there will be duplicate names with different plan and amt values. For instance: name  plan  amt smith  lrb    200 smith  l4b   300 jones  lrb    200 brown lrb    300 jackson lrb  180 johnson l4b 190 The output I'd like my query to produce would be: name     pA     pA_amt     pB     pB_amt smith     lrb     200           l4b   300 jones     lrb     200           l4b   250 brown    lrb     300 jackson  lrb     180 johnson  l4b    190 Can anyone suggest a way to do this? There will always only be a maximum possible of 2 plans and amts for any given name. I would think a join or union of some kind would work but I'm hurting my brain trying to figure it out!
Asked: Last updated:
Related Questions
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

Rightly or wrongly, my immediate approach is to create a VBA macro.
<pre>
Sub shifter()
Range(“a1”).End(xlDown).Select
For arow = ActiveCell.Row To 2 Step -1
If Cells(arow, 1).Value = Cells(arow – 1, 1).Value Then
Cells(arow – 1, 4).Value = Cells(arow, 2).Value
Cells(arow – 1, 5).Value = Cells(arow, 3).Value
Cells(arow, 1).EntireRow.Delete
End If
Next
End Sub
</pre>
This would have to be extended if you had blank lines in your data but it does the job otherwise.
Bob

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • TomLiotta
    Output like this is reasonably possible:
    NAME        MAX ( PA )  MAX ( PA_AMT )   MAX ( PB )  MAX ( PB_AMT )
brown          lrb                 300                            0
jackson        lrb                 180                            0
johnson                              0      l4b                 190
jones          lrb                 200      l4b                 250
smith          lrb                 200      l4b                 300
    That assumes that there are only values 'lrb' and 'l4b' available as plan values, that there will never more than those two exact plans, that the order isn't important, that zero is a valid output value (or that you can suppress it if it is important to do so) and that it's not important that the 'l4b' value shows under the second plan column instead of the first. It technically also assumes that DB2 is the database and that SQL is the query language. You might want to restate your requirements if anything needs to be changed. Even a minor change could result in a more complex query. Tom
    125,585 pointsBadges:
    report
  • Apickel
    Tom - Yes. All the assumptions you made are correct. 2 plans - lrb,l4b. Order is unimportant. 0 or null values are ok as well. I will always expect that if there is only one value in the plan column for a given name then that value would show up in the first plan column. It does not matter if a name has 2 plans whether or not the resulting "column order" output is lrb, l4b or l4b,lrb.
    350 pointsBadges:
    report
  • TomLiotta
    ...if there is only one value in the plan column for a given name then that value would show up in the first plan column. As you can see in my example output, johnson's single plan is 'l4b' and it shows in the second column. If it needs to be in the first column, the "simple" query I thought of won't work. It's going to be more sophisticated. What DBMS are you querying? Tom
    125,585 pointsBadges:
    report
  • Apickel
    Ugh! Access.
    350 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: