Store a SQL Server column in a variable with delimiters

Query to store the contents of a SQL Server column in a variable, inline and with delimiters.

SQL tutorial to read and store the result from a SQL Server column in a variable, and with separators. In some cases, it is useful to get all the values of a column in a T-SQL variable and add a delimiter.

How to store the contents of a SQL Server column in a variable?

To prepare a table with specific characteristics for example. For example, how to transform the standard display of a SQL Server column, like this:

• Column
• Result-1
• Result-2
• Result-3
• Result-4

And display this, i.e., the result of all selected rows in a variable with a comma or semi-colon separator for example:

Variable: Result-1, Result-2, Result-3, Result-4

To follow the flow, create the table used in the example, insert rows into it and build the query to suit the specific needs. For the full example, follow the steps below.

1. Create the customer table and insert some rows

Start by creating the customers table as an example and selecting the country column.

-- Create the customers table
CREATE TABLE [dbo].[Customers](
   [CustomerID] [int] NOT NULL,
   [FirstName] [nvarchar](20) NULL,
   [LastName] [nvarchar](20) NULL,
   [City] [nvarchar](20) NULL,
   [Country] [nvarchar](50) NULL,
   CONSTRAINT [CustomersPrimaryKeyCustomerID] 
      PRIMARY KEY CLUSTERED ([CustomerID] ASC)
);
GO
-- Insert 4 different lines
INSERT INTO dbo.Customers (CustomerID, FirstName, LastName, City, Country) 
VALUES ( 1, 'Ali','Ahmed','Cairo','Egypt');
INSERT INTO dbo.Customers (CustomerID, FirstName, LastName, City, Country) 
VALUES ( 2, 'Johnny','John','Toronto','Canada');
INSERT INTO dbo.Customers (CustomerID, FirstName, LastName, City, Country) 
VALUES ( 3, 'John','Doe','Mexico City','Mexico');
INSERT INTO dbo.Customers (CustomerID, FirstName, LastName, City, Country) 
VALUES ( 4, 'Shu','Abbas','Paris','France');

SELECT [Country] 
FROM [dbo].[Customers];

2. Query to store a column in a row with delimiters

The following query allows you to store each row of the column in a variable and to add a separator or delimiter, in this case the semicolon. And the result is a single row. The script uses the SQL Server function COALESCE.

The variable can contain 1000 characters, depending on the need, the type of the text variable must be adapted. In particular to manage strings of more than 8000 characters with a T-SQL variable.

The query is built this way:

1. Select original data in column
2. Declare the variable used to iterate
3. Select the data and store it inside the variable using the coalesce function
4. Finally display the variable content using a simple select

-- Classical selection
SELECT [Country] 
FROM   [dbo].[Customers];

-- Transform column in one line
DECLARE @result_in_line nvarchar(1000);

SELECT    @result_in_line = COALESCE(@result_in_line+';','') + [Country]
FROM    [dbo].[Customers];

SELECT    @result_in_line;

Here is the result of the script run from an SSMS Window:

Here is how to store a SQL Server column in a variable, in line with semicolon separators, rather than in a column. Indeed, this example script written in T-SQL is particularly useful for displaying separate values of a column, sorted in ascending order.

Finally, to learn some new T-SQL scripts, here is a query to convert a SQL Server XML variable to text.

Implicit conversion from data type XML to NVARCHAR T-SQL error