Please see my reply!
This article will detail how to use the transpose/pivot dataflow 2.0 which is the enhanced version of my orginal transpose dataflow. Inspired by Spencer and McWayne's groundbreaking datagrid dataflow, this dataflow will allow non-technical users to transpose/pivot much easier than before.
New Features:
Here is an example of a data set that needs to be transposed:
Here is the result datasource after the example above has been tranposed/pivoted:
is a list of columns that you do not want to unpivot, but want in the final data source
are the two new pivoted columns in the final datasouce:
are the columns you want to exclude from the final datasource.
is the name of the datasource you want to transpose
Below is an example of transform 1:
Select '`Product Number`, `Product Name`, `Product Category`, `Sale Location`' non_transpose_columns, '`Tran Date`, `Amount`' new_columns, '`Sale Location`' excluded_columns, '`product_sales`' source_table
If you want this transform to be customer facing, you can create a Domo Webform or Excel data source using the formatting described above. You will need to add the Domo Websform or Excel as a Datasource and use the following SQL for Transform 1:
Select * from DomoWebformDatasource
Make sure the webform or excel is laided out in the following format:
CREATE PROCEDURE column_transpose()BEGIN DECLARE v_new_column VARCHAR(500); DECLARE v_excluded_columns VARCHAR(500); DECLARE v_non_transpose VARCHAR(500); DECLARE v_non_transpose_q VARCHAR(500); DECLARE v_source_table VARCHAR(500); DECLARE v_finished INTEGER DEFAULT 0; DECLARE query_col VARCHAR(500); DECLARE v_table_col VARCHAR(500); DECLARE v_table_query VARCHAR(500);DECLARE transpose_cursor CURSOR for SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA= SCHEMA() AND TABLE_NAME=(select replace(source_table,'`','') from transpose_variables); DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1; SET @new_column = v_new_column; SET @excluded_columns=v_excluded_columns; SET @non_transpose=v_non_transpose; SET @non_transpose_q=v_non_transpose_q; SET @source_table=v_source_table; SET @table_col=v_table_col; SET @table_query=v_table_query;-- Sets all the variables from the user defined transformselect non_transpose_columns from transpose_variables INTO @non_transpose;select replace(excluded_columns, '`','\'') as excluded_columns from transpose_variables INTO @excluded_columns;select replace(non_transpose_columns, '`','\'') as non_transpose_q from transpose_variables INTO @non_transpose_q;select source_table as source_table from transpose_variables INTO @source_table;-- create new table based on the columns specified in the first transformselect replace(concat(non_transpose_columns,',',new_columns, ' varchar(500)'), ',', ' varchar(500),') as non_transpose_columns from transpose_variables into @table_col; SET @table_query = CONCAT('CREATE TABLE transpose (',@table_col,');');PREPARE stmt FROM @table_query;EXECUTE stmt; OPEN transpose_cursor; get_column: loop -- Get the column name value from cursor FETCH transpose_cursor INTO query_col; -- Set variable to exist loop when cursor finishes looping through the columns IF v_finished = 1 THEN LEAVE get_column; END IF; -- Set the cursor value so it can be inserted at it the new transpose column SET @query_col=CONCAT('\'',query_col,'\''); -- Set the cusrsor value with back ticks to pull the value in that column SET @query_val=CONCAT('`',query_col,'`'); -- If statement to elimated cursor values that are columns that should be excluded or not transposed (speficied in the 1st transform) IF @non_transpose_q not like (concat('\'%',query_col,'%\'')) AND @excluded_columns not like (concat('\'%',query_col,'%\'')) THEN SET @ct_sql = CONCAT('INSERT INTO transpose (SELECT ',@non_transpose,', ', @query_col, ', ',@query_val,' FROM ', @source_table ,')'); PREPARE stmt FROM @ct_sql; EXECUTE stmt; END IF; END LOOP get_column; CLOSE transpose_cursor;END;
call column_transpose();
Select * from transpose
By default this tranpose casts every column to a varchar. If you need a column to be another type (int, char, date, etc), you will need to either 1) cast your columns in your output query, or 2) write a beast mode to change the column data type
The 2 most common cast will be date and some number type (decimal, int, float)
Below is an example of casting:
select `Product Number`, `Product Name`, `Product Category`, STR_TO_DATE(`Tran Date`, '%M %y') `Tran Date`, cast(`Amount` as decimal(20)) Amount from transpose;
Click Save and Run
Here is a link to the legacy article.