question

I haven't had the chance to work with this yet - but it seems to me you're starting with a pivoted data set - so if you unpivot that set first, so you end up with the columns:

CustomerName
DateAndFunction
Value

Then you could re-pivot the set back to what you wanted...

The values in the DateAndFunction column would want to be something like 'January_Sales', 'February_Sales' etc...

edit -> I have now had the time to look at it properly:

Note that the columns don't come out in a pretty order, but, given the nature of PIVOTs (i.e. unknown columns based on data) then I think that's better handled by the client anyway.

-- Create a work table to hold the unpivoted data
CREATE TABLE #unpivotedWorkTable (
  MonthAction VARCHAR (MAX)  ,
  customer    VARCHAR (MAX)  ,
  Amount      DECIMAL (12, 2)
);

-- insert the data into the unpivoted table by using unpivot to get the set we need
INSERT INTO #unpivotedWorkTable ([MonthAction], [customer], [Amount])
SELECT DATENAME(m, DATEADD(m, [month_num] - 1, '20000101')) + '_' + [action] AS MonthAction,
       [customer],
       Amount
FROM   (SELECT 1 AS month_num, 'Steve' AS customer, 50.00 AS purchase_amt, 10.00 AS discount_amt
        UNION ALL
        SELECT 1, 'Mary', 45.00, 9.00
        UNION ALL
        SELECT 1, 'Mary', 15.00, 0.00
        UNION ALL
        SELECT 2, 'Mark', 40.00, 5.00
        UNION ALL
        SELECT 4, 'Jim', 40.00, 5.00
        UNION ALL
        SELECT 5, 'Jim', 40.00, 5.00
        UNION ALL
        SELECT 3, 'Tom', 40.00, 5.00) AS core_data 
UNPIVOT (Amount FOR [action] IN ([purchase_amt], [discount_amt])) AS unpvt;

-- now get the list of pivot columns that we will need from the unpivoted data
DECLARE @pivot_columns AS VARCHAR (MAX);
SELECT   @pivot_columns = ISNULL(@pivot_columns + ', ', '') + '[' + [MonthAction] + ']'
FROM     #unpivotedWorkTable
GROUP BY [MonthAction]

-- now use dynamic SQL to pivot the data back to how it's needed
DECLARE @sql AS VARCHAR (MAX);
SET @sql = 'SELECT * FROM #unpivotedWorkTable
PIVOT (SUM(Amount) FOR MonthAction IN (' + @pivot_columns + ')) AS pvt';
EXECUTE (@sql);

-- now drop the unpivoted work table
DROP TABLE #unpivotedWorkTable;