Using unpivot in mysql with many columns

My Input looks like this: image.png

 

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?

 

Best Answer

  • SameerSuman
    SameerSuman Member
    Answer ✓

    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

Answers

  • 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:ConfigurationConfiguration

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

     

     

     

    PreviewPreview

     

     


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • 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
    SameerSuman Member
    Answer ✓

    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

  • 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`


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • Thanks Scott for the review. I will amend the step 4 as yours! ?

     

    Regards,

    Sameer S