PIVOT Select help request 1

[Andrzejek]

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:
[tt]
SELECT PN, IN_SEC
FROM TABLE_A
WHERE (PN = 'My_PN')
AND (STATUS = 'A')
[/tt]
[pre]
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
[/pre]
I can ask separately to count how many times My_PN appears with each individual ‘IN_SEC’ piece:
[tt]
SELECT COUNT(PN) AS APR
FROM TABLE_A
WHERE (PN = 'My_PN')
AND (STATUS = 'A')
AND (IN_SEC LIKE '%[red]APR[/red]%')
[/tt]
I get: [red]41[/red]
[tt]
SELECT COUNT(PN) AS TAC
FROM TABLE_A
WHERE (PN = 'My_PN')
AND (STATUS = 'A')
AND (IN_SEC LIKE '%[blue]TAC[/blue]%')
[/tt]
I get: [blue]2[/blue]
[tt]
SELECT COUNT(PN) AS REL
FROM TABLE_A
WHERE (PN = 'My_PN')
AND (STATUS = 'A')
AND (IN_SEC LIKE '%[green]REL[/green]%')
[/tt]
I get: [green]3[/green]

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

ID ABBREV
1 DES
2 [red]APR[/red]
3 ACQ
4 CON
5 [blue]TAC[/blue]
6 PRP
7 [green]REL[/green]
8 R_R
9 CNF
[/pre]
What I am trying to get is something like this:

[pre]
PN DES [red]APR[/red] ACQ CON [blue]TAC[/blue] PRP [green]REL[/green] R_R CNF
My_PN 0 [red]41[/red] 0 0 [blue]2[/blue] 0 [green]3[/green] 0 0
[/pre]

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

 

[LKBrwnDBA]

Try this:

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>

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

 

[Andrzejek]

Great job LKBrwnDBA,

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

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

PN IN_SEC
My_PN APR
...
[/tt]
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:

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

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

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

 

[LKBrwnDBA]

Put inside the parenthesis:

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'));

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

 

[Andrzejek]

Thank you LKBrwnDBA,

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

Have fun.

---- Andy