Using unpivot in mysql with many columns

SameerSuman

My Input looks like this: 

 

I want to see my output like : (I am trying to do unpivot in mysql data transform in Domo)

 

Product Type , Date, Revenue
A , 12-31-2015, 100

B,12-31-2015, 0

 

I am trying to use stored procedure:

 

create PROCEDURE unpivot_cols()
BEGIN
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
CONCAT('select `Product Type`, ' '''', COLUMN_NAME, ''' col, ', column_name, ' as value from testing_unpivot'
) separator ' union all '
) INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS
where table_name = 'testing_unpivot'
and column_name <> 'Product Type';

set @sql = CONCAT(@sql);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END

 

When i try to execute above procedure , Isee no output. Can you help me?

 

SameerSuman

I got this one finally. Below is the sql code

 

Step 1

 

SELECT GROUP_CONCAT(
CONCAT(
' SELECT `Product Type` , ' ,QUOTE(COLUMN_NAME) , ' AS `Date`, ','`', COLUMN_NAME , '` AS `Revenue` ',
'FROM testing_unpivot'
) SEPARATOR ' UNION ALL '
) as product_info
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'testing_unpivot'
AND COLUMN_NAME <> 'Revenue Type';

 

Step 2

 

CREATE PROCEDURE procedure_new()
BEGIN

SELECT product_info into @sql2 from transform_data_1;

SET @str=concat('create table products as ',@sql2);
PREPARE q from @str;
EXECUTE q;
END

Step 3

Call procedure_new

Step 4

 

select * from products

Regards,

Sameer S

ST_-Superman-_

When I need to "unpivot" with Domo, I like to go with ETL.  There is a tile called Collapse Columns that does what you are wanting to do:Configuration

This will "unpivot" the data for you... like so: 

 

 

 

Preview

 

 

SameerSuman

I am trying to use a Dynamic solution thru below query:

 

SELECT GROUP_CONCAT(
CONCAT(
'SELECT `Product Type`, ',
QUOTE(COLUMN_NAME), ' AS `Date`, ',
'`', COLUMN_NAME, '` AS `Revenue` ',
'FROM testing_unpivot'
) SEPARATOR ' UNION ALL '
)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'testing_unpivot'
AND COLUMN_NAME <> 'Product Type';

 

It doesn't allow me to generate output. It gives an error: 
The database reported a syntax error. Incorrect column name 'GROUP_CONCAT( CONCAT( 'SELECT `Product Type`, ',<br> QUOTE(COLUMN_NAME), ' AS `Date`, ', '

 

Can you point the error here? I sincerely appreciate your response.

 

SameerSuman

I got this one finally. Below is the sql code

 

Step 1

 

SELECT GROUP_CONCAT(
CONCAT(
' SELECT `Product Type` , ' ,QUOTE(COLUMN_NAME) , ' AS `Date`, ','`', COLUMN_NAME , '` AS `Revenue` ',
'FROM testing_unpivot'
) SEPARATOR ' UNION ALL '
) as product_info
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'testing_unpivot'
AND COLUMN_NAME <> 'Revenue Type';

 

Step 2

 

CREATE PROCEDURE procedure_new()
BEGIN

SELECT product_info into @sql2 from transform_data_1;

SET @str=concat('create table products as ',@sql2);
PREPARE q from @str;
EXECUTE q;
END

Step 3

Call procedure_new

Step 4

 

select * from products

Regards,

Sameer S

ST_-Superman-_

Excellent work!  

 

I do have a few suggestions though...

 

The first is just a typo, you'll want to change the last line of Step 1 to "AND COLUMN_NAME <> 'Product Type';"

 

I would then change Step 4 because as it stands, the Date field is a VARCHAR and not formatted as a date.

 

SELECT
`Product Type`
,str_to_date(REPLACE(`Date`,'/',' '), '%m %d %Y') as `Date`
,`Revenue`

FROM products
ORDER by `Product Type`, `Date`

SameerSuman

Thanks Scott for the review. I will amend the step 4 as yours! ?

 

Regards,

Sameer S