Input
The input table, ville_temperature, contains temperature readings for the cities Nashville and Knoxville, in the state of Tennessee.
sn | city | state | period | temp_f |
---|---|---|---|---|
1 | Nashville | Tennessee | 2010-01-01 00:00:00 | 35.1 |
2 | Nashville | Tennessee | 2010-01-01 01:00:00 | 36.2 |
3 | Nashville | Tennessee | 2010-01-01 02:00:00 | 34.5 |
4 | Nashville | Tennessee | 2010-01-01 03:00:00 | 33.6 |
5 | Nashville | Tennessee | 2010-01-01 04:00:00 | 33.1 |
6 | Knoxville | Tennessee | 2010-01-01 03:00:00 | 33.2 |
7 | Knoxville | Tennessee | 2010-01-01 04:00:00 | 32.8 |
8 | Knoxville | Tennessee | 2010-01-01 05:00:00 | 32.4 |
9 | Knoxville | Tennessee | 2010-01-01 06:00:00 | 32.2 |
10 | Knoxville | Tennessee | 2010-01-01 07:00:00 | 32.4 |
SQL Call
Delimiter and IncludeColumnName have their default values.
SELECT row_id, cast(packed_data as varchar(100)), sn FROM Pack_MLE ( ON ville_temperature USING Delimiter(',') OutputColumn('packed_data') IncludeColumnName('true') TargetColumns('city', 'state', 'period', 'temp_F') ) AS dt ORDER BY sn;
Output
The columns specified by TargetColumns are packed in the column packed_data. Virtual columns are separated by commas, and each virtual column value is labeled with its column name. The input column sn, which was not specified by TargetColumns, is unchanged in the output table.
row_id packed_data sn ------ --------------------------------------------------------------------- -- 3 city:nashville,state:tennessee,period:2010-01-01 00:00:00,temp_f:35.1 1 5 city:nashville,state:tennessee,period:2010-01-01 01:00:00,temp_f:36.2 2 2 city:nashville,state:tennessee,period:2010-01-01 02:00:00,temp_f:34.5 3 2 city:nashville,state:tennessee,period:2010-01-01 03:00:00,temp_f:33.6 4 1 city:nashville,state:tennessee,period:2010-01-01 04:00:00,temp_f:33.1 5 3 city:knoxville,state:tennessee,period:2010-01-01 03:00:00,temp_f:33.2 6 1 city:knoxville,state:tennessee,period:2010-01-01 04:00:00,temp_f:32.8 7 4 city:knoxville,state:tennessee,period:2010-01-01 05:00:00,temp_f:32.4 8 6 city:knoxville,state:tennessee,period:2010-01-01 06:00:00,temp_f:32.2 9 7 city:knoxville,state:tennessee,period:2010-01-01 07:00:00,temp_f:32.4 10
Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.