ZIP

Use the Zip command to aggregate a single column, normally made of JSON, XML, or CSV fragments into a single document. This operation groups all selected records into a single row; except for the column being aggregated.

This command supports a few options that control the groupping operation that vary based on the format selected.

Available Options:

• root: specifies an XML root name
• element: specifies an XML element name for each row
• trim: when 1, trims each row
• flatten: when 1, flattens the output
• array: when 1, indicates a JSON Array should wrap the content
• headers: when 1, indicates a CSV header should be added if possible

Aggregates the content of a string column to build CSV, JSON or XML documents.

ZIP COLUMN 'field'
{ BATCH N }
{ FORMAT [ JSON | XML | CSV ] }
{ OPTIONS '...' }
{ ORDER BY '...' }
{ WHEN '...' }
{ WITH 
	{ IGNORE_IF_EXISTS }
	{ CONTINUE_ON_ERROR }
};

COLUMN	The name of the column to zip; the column name is case-insensitive
FORMAT	The expected rendering format (csv,json,xml) - default is CSV
WHEN	An optional SQL-like filter that selects which rows should be zipped
BATCH	When greater than 1, the number of rows to group at a time
ORDER BY	When specified, the order in which rows will be zipped
OPTIONS	Options specific to CSV, JSON or XML formatting
CONTINUE_ON_ERROR	Continues processing even if errors are detected

Example 1

SELECT * FROM DB [sql] (select * from sys.databases);

-- rename a column
ADD OR ALTER COLUMN 'id = {{database_id}}';

-- Creates arrays of json objects, 100 records at a time, on 
-- a single line
ZIP COLUMN 'jsonOutput' FORMAT JSON BATCH 100 OPTIONS 'array:objects flatten:1'; 

Example 2

SELECT * FROM DB [sql] (select * from sys.databases);


-- Creates a comma-delimited list of all available records and auto-trim
ZIP COLUMN 'csvOutput' FORMAT CSV OPTIONS 'delimited:, trim:1'; 

Example 3

SELECT * FROM DB [sql] (select * from sys.databases);


-- Creates an XML document with a root node called Header
ZIP COLUMN 'xmlOutput' FORMAT XML OPTIONS 'root:Header';