Pivot query in Oracle

Hello

I have a table which has membership data in it. I'd like to be able to run a query against it that will only return member_ids which only have an end date.

Here is the table structure:

MBRATTRVAL_ID  NUMBER
STOREENT_ID    INTEGER
MEMBER_ID      NUMBER
ATTRTYPE_ID    VARCHAR2
MBRATTR_ID     NUMBER
FLOATVALUE     NUMBER
INTEGERVALUE   INTEGER
STRINGVALUE    VARCHAR2
DATETIMEVALUE  TIMESTAMP

The only unique identifier for each record is a member_id. Here is an example:

MBRATTRVAL_ID  STOREENT_ID  MEMBER_ID  ATTRTYPE_ID  MBRATTR_ID  FLOATVALUE  INTEGERVALUE  STRINGVALUE        DATETIMEVALUE
0              NULL         123456789  STRING                1  NULL        NULL          1100123456789
1              NULL         123456789  STRING                2                            free
3              NULL         123456789  DATETIME              3                                               1/1/2013 12:00:00.000000 AM
4              NULL         123456789  DATETIME              4                                               12/31/2323 11:59:59.000000 PM
2              NULL         123456789  STRING                5                            0
9              NULL         987654321  DATETIME              4                                               12/31/2007 11:59:59.000000 PM
10             NULL         567898765  STRING                1  NULL        NULL          1100987654321
11             NULL         567898765  STRING                2                            free
13             NULL         567898765  DATETIME              3                                               3/2/2010 12:00:00.000000 AM
14             NULL         567898765  DATETIME              4                                               3/30/2018 11:59:59.000000 PM

Open in new window


As you can see in the example data above, MEMBER_ID 987654321 only has a MBRATTR_ID 4. What I'd like to do is run a query that will only retreive those entries where the given member ONLY has MBRATTR_ID 4.
Larry GrovesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
I don't think you need a PIVOT here.

Maybe something like this (untested)
select member_id from (
   select member_id, mbrattr_id, count(*) over(partition by member_id) my_count from membership_table
)
where mbrattr_id=4 and my_count=1
/

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Larry GrovesAuthor Commented:
Thanks slightwv! I really appreciate the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.