How to combine 2 rows to multiple columns?

Apickel

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: October 27, 2011 4:05 PM Last updated: October 29, 2011 6:23 AM

Answer Wiki

Last updated: October 29, 2011 6:23 AM GMT

BobBeechey750 pts.
History
Contributors
- Ordered by most recent
- BobBeechey750 pts.

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

TomLiotta Oct 28, 2011 0:18 AM GMT
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 Oct 28, 2011 10:39 AM GMT

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 Oct 28, 2011 7:04 PM GMT

...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 Oct 28, 2011 9:21 PM GMT

Ugh! Access.

350 pointsBadges:

report