Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Help with a SQL report using PIVOT

ArkatktMay 11 2016 — edited May 13 2016

I have built a report using the PIVOT method to show version numbers of all my environments.  The results are fine, but I would like to take it one step further.  I would like to have every Group/name on one line instead of multiple.  SQL and results:

SELECT IC_GROUP,

  IC_NAME,

  PROD,

  QA1,

  QA2,

  QA3,

  ODY,

  CSEQA,

  STAGE,

  LE_PROD,

  LE_STAGE

  FROM (SELECT IC_GROUP,

  IC_NAME,

  (CASE WHEN PROD = 1 THEN version_num ELSE NULL END) AS PROD,

  (CASE WHEN QA1 = 1 THEN version_num ELSE NULL END) AS QA1,

  (CASE WHEN QA2 = 1 THEN version_num ELSE NULL END) AS QA2,

  (CASE WHEN QA3 = 1 THEN version_num ELSE NULL END) AS QA3,

  (CASE WHEN ODY = 1 THEN version_num ELSE NULL END) AS ODY,

  (CASE WHEN CSEQA = 1 THEN version_num ELSE NULL END) AS CSEQA,

  (CASE WHEN STAGE = 1 THEN version_num ELSE NULL END) AS STAGE,

  (CASE WHEN LE_PROD = 1 THEN version_num ELSE NULL END)

  AS LE_PROD,

  (CASE WHEN LE_STAGE = 1 THEN version_num ELSE NULL END)

  AS LE_STAGE

  FROM (  SELECT *

    FROM (  SELECT IC_GROUP,

    IC_NAME,

    IV_VERSION_NUMBER,

    IV_VERSION_NUMBER AS VERSION_NUM,

    IE_NAME

  FROM metrics.INV_VERSIONS,

    metrics.INV_ENVIRONMENTS,

    metrics.INV_STACKS,

    metrics.INV_COMPONENTS

    WHERE  IS_ID =1000

    AND IV_IE_ID = IE_ID

    AND IV_IS_ID = IS_ID

    AND IV_IC_ID = IC_ID

    ORDER BY IC_NAME) PIVOT (COUNT (IV_VERSION_NUMBER)

    FOR (IE_NAME)

    IN ('PRODUCTION' AS PROD,

    'QA1' AS QA1,

    'QA2' AS QA2,

    'QA3' AS QA3,

    'ODYSSEY' AS ODY,

    'CSEQA' AS CSEQA,

    'STAGING' AS STAGE,

    'LE PROD' AS LE_PROD,

    'LE STAGING' AS LE_STAGE))

  ORDER BY 1, 2)) X

Sample result now:

IC_GroupIC_NAMEPRODQA1QA2QA3ODYCSEQASTAGE
Group1API1.11.02
Group1API1.01
Group1API1.02
Group2IMPORT2.02.11.8
Group2IMPORT1.02
Group2IMPORT
Group3EXPORT1.02
Group3EXPORT1.02
Group3EXPORT1.02

So what I would like to see is Group1/Name all show in one line and not across 3 (like for API).  I have been trying to use other methods like WITHIN GROUP and LISTAGG but can't see to crack this.  Any assistance?

Oracle 11gR2 is my version.

This post has been answered by CarlosDLG on May 11 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 10 2016
Added on May 11 2016
9 comments
400 views