Excel’s CONCATENATE / CONCAT function — create a SUM(CASE WHEN) SQL statement

Image of software function sign fx
Purpose
To join text and/or numbers together. The values can be entered directly into the function, or pulled in from cell contents by using cell references. The result is a text string.


Syntax example — join the contents of cells B3 and C3 with text strings.

= CONCATENATE(“The summary for “,B3,” was “, C3)

Result: “The summary for November was £3,500”

[where B3 contains ‘November’, C3 contains ‘£3,500’]

Example – create a ‘sum(case when …)’ series of statements for SQL

The structure for a SELECT SUM(CASE WHEN…) statement in SQL is:

SUM(CASE WHEN [variable] [condition] THEN [result1] ELSE [result2] END)

,SUM (CASE WHEN CUST_ID = ‘100’ THEN ORDER_QUAN ELSE 0 END) as ‘100’

The above example finds the order quantity (ORDER_QUAN) for customer ID 100 (CUST_ID) and labels the row/record in the resulting table as the customer ID 100.

You can use Excel’s CONCATENATE/CONCAT function to generate a list of SQL statements, as shown below.


Video player icon
Method (see video above)

The method uses the newer version of CONCATENATE( ), CONCAT( ), which is gradually being phased in for users of Office 365, and Windows and Mac users of Office 2019.

  1. Enter the separate chunks of text and values / Cell references into cells.
  2. Use the CONCAT function to combine the range of cells to create the desired text string.
  3. Fill the cells down the rows as required.
Note: how to include a single quote at the start of a cell in Excel
  • To include a single quote [‘] at the start of a cell there are two options (as shown in the video above):
    • add an extra single quote [‘ ‘], or
    • change the cell format to ‘Custom: ‘@‘ — click on the right-hand corner of the cell format section of the ribbon, and in the ‘Format Cells’ dialog box, choose ‘Custom’, and enter ‘@ [single quote followed by ampersand].

Because Excel interprets a single quote [‘] at the start of a cell in a particular way (i.e. it turns the cell contents after the [‘] to text, but doesn’t include the [‘] in the actual text string), you need to add an extra single quote.

This is useful in some circumstances. For example, to enter a formula in a cell and prevent Excel from running it (and potentially throwing up errors) while you’re typing it in, you enter a [‘] at the start of the cell which denotes the contents as text, and not a formula or function to run. When you are ready to run the formula or function, you can delete the [‘] at the start of the cell.