How to Pivot Column to Row in MicroStrategy Developer

Question

This is the table that I have

Contract_ID	Period	Forecast	Shortfall
1	May23	35325	456
2	June24	3466	768
3	Sept22	457657	879

I need the output as:

Contract_ID	May23Forecast	May23Shortfall	June24Forecast	June24Shortfall	Sept22Forecast	Sept22Shortfall	Total Forecast	Total Shortfall
1	35325	456					35325	456
2			3466	768			3466	768
3					457657	879	457657	879

In MicroStrategy Developer.

I'm able to achieve this by placing the period attribute on the row level for forecast but I'm not able to add another metric after that.

Any suggestions, anyone?

Answer 1

So, (Not a MicroStrategy Specific Answer) That said, I always believe that locating business logic closer to the database has benefits.

--If you know the periods (If they are fixed this is easy)

SELECT 
,   Contract_ID
,   CASE WHEN PERIOD = 'May23'
         THEN Forecast
         ELSE NULL
    END AS May23Forecast
,   CASE WHEN PERIOD = 'May23'
         THEN Shortfall
         ELSE NULL
    END AS May23Shortfall
,   CASE WHEN PERIOD = 'June24'
         THEN Forecast
         ELSE NULL
    END AS June24Forecast
,   CASE WHEN PERIOD = 'June24'
         THEN Shortfall
         ELSE NULL
    END AS June24Shortfall
...
FROM YOURTABLE
;

Etc.

-- IF you are not certain what periods their are, but you know how many (Say 12 a year) something like this can be done

SELECT 
    Contract_ID
,   Period
,   MAX(CASE WHEN PERIOD_COUNTER = 1
         THEN Forecast
         ELSE NULL
    END) AS FORCASt1
,   MAX(CASE WHEN PERIOD_COUNTER = 1
         THEN Shortfall
         ELSE NULL
    END) AS Shortfall1
,   MAX(CASE WHEN PERIOD_COUNTER = 2
         THEN Forecast
         ELSE NULL
    END) AS FORCASt1
,   MAX(CASE WHEN PERIOD_COUNTER = 2
         THEN Shortfall
         ELSE NULL
    END) AS Shortfall1
...
FROM YOURTABLE
CROSS JOIN
(SELECT 
    PERIOD
,   ROW_NUMBER() OVER(ORDER BY PERIOD) AS PERIOD_COUNTER
FROM YOUR TABLE
GROUP BY 1
)
;