Extracting multiple (comma-separated) values from data columns in SQL Server

In some cases, you may have a table with a column containing comma-separated values. To extract distinct values from this column and use them in a Pivot Widget, you can use the ‘STRING_SPLIT’ function in SQL Server.

Splitting Comma-Separated Values into rows

Note: Using this query you can create a view in the sqlserver database and use it in bold bi for better performance

SELECT
  [tablename].[columnname] AS [columnname], Split.value as splitValue
FROM [dbo].[tablename] AS [tablename]
CROSS APPLY STRING_SPLIT([tablename].[columnname], ',') as Split

Replace ‘[tablename]’ and ‘[columnname]’ with your table and column names.

Example

Consider the following table:

To split the ‘Items’ column, use the following query:

SELECT
  Orders.Items AS Items, Split.value as splitValue
FROM [dbo].[Orders] AS Orders
CROSS APPLY STRING_SPLIT(Orders.Items, ',') as Split

The result will be:

Now you can use the ‘splitValue’ column in your pivot widget to show the count of orders.

Splitting Comma-Separated Values into columns

Consider the following table:

To split the ‘EmpPhoneNo’ column, use the following query:

SELECT EmpId,
EmpPhoneNo, 
ISNULL([phn1],'') AS [phn1], 
ISNULL([phn2],'') AS [phn2], 
ISNULL([phn3],'') AS [phn3], 
ISNULL([phn4],'') AS [phn4] 
FROM ( 
 SELECT EmpId, 
 EmpPhoneNo, 
 'Phn'+ CAST(ROW_NUMBER()OVER(PARTITION BY EmpId ORDER BY EmpId) AS VARCHAR) AS Col, 
 Split.value 
 FROM dbo.PhoneNumberList AS Emp 
 CROSS APPLY String_split(EmpPhoneNo,',') AS Split ) 
 AS tbl
Pivot (Max(Value) FOR Col IN ([phn1],[phn2],[phn3],[phn4])
) AS Pvt

The result will be:

Related Articles:

• STRING_SPLIT (Transact-SQL) - SQL Server | Microsoft Docs
• Configuring Expression columns