0

I have a large database that is connected to a forms mapping program. In order for me to map results from SQL queries I have to put a single line field in for each result row. It defaults to row 0 and if there are more than one result I need to manual change the index of the field to 1,2,3,4 etc... The current data I am trying to query is for a table that looks like this:

+---------------+----------------+---------------+----------------+
|     Name      | Account Number | Accout Role 1 | Account Role 2 |
+---------------+----------------+---------------+----------------+
| John Smith    |          12345 | TaxSigner     |                |
| John Smith    |          12345 |               | TaxOwner       |
| Tim Anderson  |         678920 | TaxSigner     |                |
| Tim Anderson  |         678920 |               | TaxOwner       |
| Bill Williams |         510301 | TaxSigner     |                |
| Bill Williams |         510301 |               | TaxOwner       |
| Frank Stevens |         897090 | TaxSigner     |                |
+---------------+----------------+---------------+----------------+

But I need it to look like this:

+---------------+----------------+---------------+----------------+
|     Name      | Account Number | Accout Role 1 | Account Role 2 |
+---------------+----------------+---------------+----------------+
| John Smith    |          12345 | TaxSigner     | TaxOwner       |
| Tim Anderson  |         678920 | TaxSigner     | TaxOwner       |
| Bill Williams |         510301 | TaxSigner     | TaxOwner       |
| Frank Stevens |         897090 | TaxSigner     |                |
+---------------+----------------+---------------+----------------+

There could be some customers with a single role while others could have both. I am able to merge the duplicate rows into a single column but that won't work. I need each role to end up in a separate column.

0

1 Answer 1

1
SELECT `Name`
     , `Account Number`
     , MAX(`Accout Role 1`) `Accout Role 1`
     , MAX(`Account Role 2`) `Accout Role 2`
FROM MyTable
GROUP BY `Account Number` -- , `Name`

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.