Reshape

Pivot: Transpose unique values in a single column to generate multiple columns, aggregating as needed. The pivot will dynamically generate a column per unique value, or you can pass a list_of_vals with the unique values you wish to create columns for. Unpivot: Opposite of pivot, transforms a table by transforming columns into rows

Parameters

Name	Type	Description	Is Optional
method	string	Method to reshape table 'pivot' to transpose unique values in a column to generate multiple columns or 'unpivot' to do the opposite.
values	column	column to pivot and aggregate. Required for 'pivot' method.	True
columns	column	Pivot: column with row values that will become columns. Required for 'pivot' method. Unpivot: List of columns in the source table that will be narrowed into a single pivot column. The column names will populate name_column, and the column values will populate value_column.	True
agg_method	agg	method of aggregation (i.e. sum, avg, min, max, etc.). Required for 'pivot' method.	True
value_column	string	The name to assign to the generated column that will be populated with the values from the columns in the column list. Required for 'unpivot' method.	True
name_column	string	The name to assign to the generated column that will be populated with the names of the columns in the column list. Required for 'unpivot' method.	True
dimensions	column_list	Applicable to 'pivot' method only. Dimension columns after the pivot runs.	True
list_of_vals	string_list	optional argument to override the dynamic lookup of all values in the value_column and only pivot a provided list of values	True

Example

Pull a source Dataset and preview it:

ds = rasgo.get.dataset(id)
print(ds.preview())

	DATE	SYMBOL	CLOSE
0	1990-09-26	APA	4.8797
1	1990-08-06	APA	4.3312
2	1990-06-12	APA	3.8123
3	2007-10-19	XRX	36.0728
4	2007-08-20	XRX	34.4498
5	2007-07-10	XRX	42.5646
6	2007-03-07	XRX	36.3541
7	2007-03-01	XRX	37.1547
8	2007-01-04	XRX	36.5489
9	2006-07-13	XRX	29.7325

Transform the Dataset and preview the result:

ds2 = ds.reshape(
  dimensions=['DATE'],
  values='CLOSE',
  columns='SYMBOL',
  agg_method='AVG',
  list_of_vals=['JP','GOOG','DIS','APLE'],
  method='pivot'
)
ds2.preview()

ds3 = ds2.reshape(
  value_column="CLOSE",
  name_column="SYMBOL",
  columns=['JP','GOOG','DIS','APLE'],
  method='unpivot'
)
ds3.preview()

	DATE	JP	GOOG	DIS	APLE
0	2013-07-26		441.022	59.0729
1	2017-11-21	19.7923	1034.49	99.3686	16.48
2	2006-11-17		248.464	27.0257
3	2005-09-12		154.292	20.39
4	1999-07-01			22.1751
5	1998-02-03			27.3093
6	1996-04-22			15.2845
7	2017-07-25	7.221	950.7	102.668	15.0863
8	2006-12-08		241.151	28.2154
9	2016-12-12	8.1188	789.27	99.65	15.739

Source Code

https://github.com/rasgointelligence/RasgoTransforms/blob/main/rasgotransforms/rasgotransforms/transforms/reshape/snowflake/reshape.sql