SQL (and JPA) Sorting parameters on a denormalised table

Question

I have inherited a legacy reporting tool and have a denormalised table, that stores the ids of a team of sale guys, as well as fields describing their details.

The table is like this (simplified number of fields here):

Table Salers: salerId, fieldId, value

And we have data like

(1, 1, Doe), (1, 2, John), (2, 1, Smith), (2, 2, Robert), (3, 1, White), (3, 2, Alan)

I need to get the list of salers sorted by last name and first name.

I am not a SQL expert and I cannot figure out how to get this in a generic way (because the table contains much more than firstname and last names types of fields).

In addition I am using JPA on top of the DB, and have a Class describing these fields. I still don't see how I could write such a query with JPA.

class SaleInformation {
    private int salerID;
    private int fieldID;
    private String value;
}

Any idea?

Thanks in advance

Gilles

Answer 1

I can help You just in the SQL code:

select salerID, [2] LastName, [1] FirstName
from Salers s
pivot 
(
  max(value) for fieldID in ([1], [2])
) Result
order by [2], [1]

OR if You can't use the PIVOT operator:

select ISNULL(FirstName.salerID, LastName.salerID) salerID
  , LastName.value LastName
  , FirstName.value FirstName
from (
  select *
  from Salers s
  where s.fieldID = 1) FirstName
full outer join (
  select *
  from Salers s
  where s.fieldID = 2) LastName on FirstName.salerID = LastName.salerID
order by LastName.value, FirstName.value