INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

PIVOT Select help request

PIVOT Select help request

(OP)
I believe I need a PIVOT Select statement, but I have never done it.

Here is what I have:
I have a TABLE_A where I can do:

SELECT PN, IN_SEC
FROM TABLE_A
WHERE (PN = 'My_PN')
AND (STATUS = 'A')

PN	IN_SEC
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR, REL
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	TAC, REL
My_PN	TAC, REL
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
My_PN	APR
 
I can ask separately to count how many times My_PN appears with each individual ‘IN_SEC’ piece:

SELECT COUNT(PN) AS APR
FROM TABLE_A
WHERE (PN = 'My_PN')
AND (STATUS = 'A')
AND (IN_SEC LIKE '%APR%')

I get: 41

SELECT COUNT(PN) AS TAC
FROM TABLE_A
WHERE (PN = 'My_PN')
AND (STATUS = 'A')
AND (IN_SEC LIKE '%TAC%')

I get: 2

SELECT COUNT(PN) AS REL
FROM TABLE_A
WHERE (PN = 'My_PN')
AND (STATUS = 'A')
AND (IN_SEC LIKE '%REL%')

I get: 3

I do have a table (MY_STATUS) where all those ‘pieces’ are listed like this:
SELECT ID, ABBREV
FROM MY_STATUS
ORDER ID

ID	ABBREV
1	DES
2	APR
3	ACQ
4	CON
5	TAC
6	PRP
7	REL
8	R_R
9	CNF
 

What I am trying to get is something like this:

PN     DES APR ACQ CON TAC PRP REL R_R CNF
My_PN    0  41   0   0   2   0   3   0   0
 

All other fields where the count is zero could show 0 or nothing

Any pointers to the right place will be much appreciated.

Have fun.

---- Andy

RE: PIVOT Select help request



Try this:

CODE

SQL> WITH table_a (pn, in_sec)
  2       AS (SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
  3           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
  4           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
  5           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
  6           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
  7           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
  8           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
  9           SELECT 'My_PN', 'APR, REL' FROM DUAL  UNION ALL
 10           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 11           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 12           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 13           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 14           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 15           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 16           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 17           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 18           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 19           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 20           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 21           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 22           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 23           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 24           SELECT 'My_PN', 'TAC, REL' FROM DUAL UNION ALL
 25           SELECT 'My_PN', 'TAC, REL' FROM DUAL UNION ALL
 26           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 27           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 28           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 29           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 30           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 31           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 32           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 33           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 34           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 35           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 36           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 37           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 38           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 39           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 40           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 41           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 42           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 43           SELECT 'My_PN', 'APR' FROM DUAL UNION ALL
 44           SELECT 'My_PN', 'APR' FROM DUAL)
 45     , my_status (id, abbrev)
 46       AS (SELECT '1', 'DES' FROM DUAL UNION
 47           SELECT '2', 'APR' FROM DUAL UNION
 48           SELECT '3', 'ACQ' FROM DUAL UNION
 49           SELECT '4', 'CON' FROM DUAL UNION
 50           SELECT '5', 'TAC' FROM DUAL UNION
 51           SELECT '6', 'PRP' FROM DUAL UNION
 52           SELECT '7', 'REL' FROM DUAL UNION
 53           SELECT '8', 'R_R' FROM DUAL UNION
 54           SELECT '9', 'CNF' FROM DUAL)
 55  SELECT *
 56    FROM (SELECT a.pn, s.abbrev
 57            FROM table_a a, my_status s
 58           WHERE INSTR ( a.in_sec, s.abbrev) > 0)
 59   PIVOT (COUNT ( pn) FOR abbrev IN('DES','APR','ACQ','CON','TAC','PRP','REL','R_R','CNF'))
 60  /

     'DES'      'APR'      'ACQ'      'CON'      'TAC'      'PRP'      'REL'      'R_R'      'CNF'
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         0         41          0          0          2          0          3          0          0

SQL> 
3eyes

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

RE: PIVOT Select help request

(OP)
Great job LKBrwnDBA, smile

As you can see, I presented the data for 'My_PN' only:

SELECT PN, IN_SEC
FROM TABLE_A
WHERE (PN = 'My_PN')
AND (STATUS = 'A')

PN IN_SEC
My_PN APR
...

but in my TABLE_A I will have more than just My_PN

How should I modify your SQL to get the count for My_PN?

Something like:

SELECT *
FROM (SELECT a.pn, s.abbrev
    FROM table_a a, my_status s
    WHERE INSTR ( a.in_sec, s.abbrev) > 0)
    AND a.pn = 'My_PN'
PIVOT (COUNT ( pn) FOR abbrev IN('DES','APR','ACQ','CON','TAC','PRP','REL','R_R','CNF'))
  

I did try the RED line of SQL, but I get an error: "ORA-00933: SQL command not properly ended" sad

I kind of tent to lean to a View where I can hard-code all MY_STATUS.ABBREV columns and have several Count(…) as ‘DES’, Count(…) as ‘APR’, etc

Have fun.

---- Andy

RE: PIVOT Select help request


Put inside the parenthesis:

CODE

SELECT *
FROM (SELECT a.pn, s.abbrev
    FROM table_a a, my_status s
    WHERE INSTR ( a.in_sec, s.abbrev) > 0
      AND a.pn = 'My_PN')
PIVOT (COUNT ( pn) FOR abbrev IN('DES','APR','ACQ','CON','TAC','PRP','REL','R_R','CNF')); 
noevil


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

RE: PIVOT Select help request

(OP)
Thank you LKBrwnDBA,

My mistake of placing this one line outside parenthesis.
Now it works like a dream smile

Have fun.

---- Andy

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close