Searched a lot for this but never found the end to end solution, so I’m sharing here. 

I have a case where I want to send an employee the results from a SQL Server query (table records) on a regular basis, but haven’t been able to piece it together until now. So here’s an overview I hope is useful for someone else out there:
 

1. Trigger: Schedule by Zapier - running every week. 
2. Action: SQL Server - Find row via Custom Query for the results you want
   (e.g. SELECT * FROM SALESORDER WHERE Employee=”Betty” For JSON AUTO, INCLUDE_NULL_VALUES).
   - The trick here is to put “FOR JSON AUTO, INCLUDE_NULL_VALUES” at the end of your query. This returns a single JSON field for Zapier as a result and includes Null values (in case you have blanks).
   - Note: there is a character limit on this JSON field from SQL Server (~for me it was 2000 characters) so beware this won’t work with large tableset results - you’d need to do multiple queries and concat them later in Zapier - the 2000 character limit was enough for ~10 records in my sample approach.
3. Action: Code by Zapier: For this step you’ll need to run some simple Javascript to parse the JSON in multiple line items that can be understood by Zapier: 
   - Set up a Code by Zapier step using JavaScript
   - Set up your Input Data variable name as InputData and the value to be your JSON result field from Step 2.
   - Use the following code: 
   let smooth = JSON.parse(InputData);
   output = .{value: smooth}]
   - Test the Action and you should find that all the records are now mult-line item results for Zapier.
   Example:
   value:
             1: 
                  Name: Jon Smith
                  Email: jsmith@email.com
                  Amount: 10.00
             2: 
                  Name: Jane Smith
                  Email: jasmith@email.com
                  Amount: 60.00
             3: 
                  Name: Dane Young
                  Email: dyoung@email.com
                  Amount: 100.00
4. Create a subsequent step that uses multi-line item output (e.g. a Google Sheet) you can now add multi-line item fields as per standard Zapier approach: https://zapier.com/help/create/basics/use-line-items-in-zaps
   I used a google sheet with fields Name, Email, Amount and placed the fields from Step 3 in them and all multiple returned records populate in the sheet.

TL;DR:

The two pieces of magic here are:

1. Use “FOR JSON AUTO” at the end of a SQL Server statement to receive the results in a single JSON formatted field.
2. Use Javascript JSON.parse(InputData); output =  {value: smooth}] to return the JSON in a multi-line item format Zapier can understand.