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.
biglarrrrAsked:
Who is Participating?
 
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
 
biglarrrrAuthor Commented:
Thanks slightwv! I really appreciate the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.