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_Group | IC_NAME | PROD | QA1 | QA2 | QA3 | ODY | CSEQA | STAGE |
---|
Group1 | API | 1.1 | | | | | 1.02 | |
Group1 | API | | 1.01 | | | | | |
Group1 | API | | | 1.02 | | | | |
Group2 | IMPORT | 2.0 | 2.1 | | | | | 1.8 |
Group2 | IMPORT | | | | 1.02 | | | |
Group2 | IMPORT | | | | | | | |
Group3 | EXPORT | | | | | 1.02 | | |
Group3 | EXPORT | | 1.02 | | | | | |
Group3 | EXPORT | | | | | | 1.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.