mercredi 21 octobre 2015

Alias table that's actually a pivoted one?

I have this query:

SELECT
    A.USERID 
    A.NAME
    PVT.PHONE 'PROBABLY A CASE STATEMENT ON NULL WILL GO HERE...
    PVT.ADDRESS 'ON HERE AS WELL...
FROM
    USERS A
    'I NEED TO CREATE A PIVOT TABLE HERE WITH THE ALIAS OF 'PVT' ON TABLE 'B'

B Contents:

 UserID  PHONE          ADDRESS      TYPE
 1       444-555-2222   XXXXXXX      PHONE
 1       XXXXXXX        66 Nowhere   NOTADDRESS 

I want, on the same row, the user's phone by getting B.PHONE if TYPE = 'PHONE'.

I also want, on the same row, the user's address by getting B.ADDRESS content if TYPE = 'ADDRESS'.

As you see in the table dump above, I don't have a record matching the user ID AND TYPE = 'ADDRESS'

So I would need to show a blank or 'No address' in the main SELECT which will show the phone, but on the same row, blank or 'No address'.

I don't want to create an INNER JOIN because if there are no matching UserID's in B, the query will not return the info that I have in table A for that user.

Also, a LEFT JOIN will create two rows, which I don't want.

I think I pivoted table as alias would do it, but I don't know how to create such an alias.

Any ideas ?

Aucun commentaire:

Enregistrer un commentaire