CODEWITHSUNDEEP
oracle-databasepivot
John
John

Reputation: 299

Oracle Pivot options

I have the below Pivot and output. I would like to display the below.

  1. Remove the parentheses around the columns?
  2. Add indicator of X and Null in substitute of 1 and 0?

SQL:

SELECT DISTINCT
*
   FROM (
          SELECT D.ID, D.DI, A.ID
          FROM  A
          LEFT JOIN  AD                  ON A.ID = AD.ID
          LEFT JOIN  D                        ON AD.ID = D.ID
          WHERE 1=1
          AND A.ID = 890929
                                           )
          PIVOT
            (
           COUNT(ID) 
            FOR DI IN  ( 'Low med','Soft','Regular','High Med','Other')
              )

Query output:

ID  'Low med' 'Soft' 'Regular'  'High Med'  'Other'
1      1         1      0           0          1

Expected output:

ID  LOW_MED    SOFT REGULAR        HIGH_MED       OTHER
1      X         X      NULL           NULL          X

Upvotes: 0

Views: 40

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

You can remove the single quotes (not parentheses, which are ()), by aliasing the pivoted expressions:

FOR DI IN ('Low med' as low_med, 'Soft' as soft, 'Regular' as regular,
  'High Med' as high_med,'Other' as other)

You can then use those aliases for the second part, but adding case expressions to your main query:

SELECT id,
  case when low_med = 1 then 'X' else null end as low_med,
  case when soft = 1 then 'X' else null end as soft,
  case when regular = 1 then 'X' else null end as regular,
  case when high_med = 1 then 'X' else null end as high_med,
  case when other = 1 then 'X' else null end as other
FROM (
  SELECT D.ID, D.DI, A.ID
  FROM  A
  LEFT JOIN  AD ON A.ID = AD.ID
  LEFT JOIN  D ON AD.ID = D.ID
  WHERE 1=1
  AND A.ID = 890929
)
PIVOT
(
  COUNT(ID) 
  FOR DI IN ('Low med' as low_med, 'Soft' as soft, 'Regular' as regular,
    'High Med' as high_med,'Other' as other)
)

Upvotes: 1

Related Questions