how to combine multiple queries

Question

I have the following table below:

Table name: STAT

Region   M07  M08 M09 
---------------------
P1       0    1   0    
P2       0    0   0   
P3       2    0   0    
P4       0    0   0     
P5       0    0   0     
P6       0    0   0   
P7       9    0   3  

I UNPIVOTED the months columns on the TABLE STAT, which contain numerical values. Obtained Result

---------------------
Region   Month  Qty
---------------------
P1      M07    0
P1      M08    1
P1      M09    0
P2      M07    0
P2      M08    0
P2      M09    0

The UNPIVOT was done using this code

select Region, month, qty FROM
(
    SELECT Region, 'M07' AS month, M07 AS qty from STAT
    UNION ALL
    SELECT Region, 'M08' AS month, M08 AS qty from STAT
    UNION ALL 
    SELECT Region, 'M09' AS month, M09 AS qty from STAT
)x

I would now need to add a new column to this unpivoted data set. For e.g. a column named "PROFILE". I tried writing the code as

ALTER TABLE STAT
ADD COLUMN `Profile` CHAR(8) NOT NULL DEFAULT 'MI';

Then I tried to display the results of the unpivoted dataset and new column added by writing

SELECT * from STAT

However I am now seeing the new column added to the original dataset, but I cannot see the unpivoted data. I would like to see the unpivoted data and final new column added. I am assuming I need to write an ALTER table somewhere in the unpivot part of the code. Some help here would be appreciated ?

Answer 1

You need to actually create a new table to store the unpivoted data. This can be achieved by wrapping you query in CREATE TABLE ... AS SELECT statement.

CREATE TABLE stat2 AS
SELECT region, 'M07' AS month, M07 AS qty from stat
UNION ALL
SELECT region, 'M08', M08 AS from stat
UNION ALL 
SELECT region, 'M09', M09 AS from stat;

Then you can do :

ALTER TABLE STAT2
ADD COLUMN `Profile` CHAR(8) NOT NULL DEFAULT 'MI';

And, of course :

SELECT * from STAT2;