How to combine 2 rows to multiple columns?

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!

Answer Wiki

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

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

Processing your response...

Discuss This Question: 4 &nbspReplies

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

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

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

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.

...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

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

Processing your reply...

Ask a Question

Free Guide: Managing storage for virtual environments

Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!

Share this item with your network:

To follow this tag...

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy