MS SQL String Splits

String to rows

Code

Using the useful function STRING_SPLIT(), we can convert a text to table rows: DECLARE @myvariable varchar(MAX) = 'a,b,c,d,e,f,g'; SELECT [value] FROM STRING_SPLIT(@myvariable, ',')

Result

This produces the following table:

	value
1	a
2	b
3	c
4	d
5	e
6	f
7	g

Concatenate rows

Setup

Let's say I have the following table: #myTable

	Name	Code	Price
1	Alex	111	1
2	Bob	121	2
3	Alex	131	3
4	Callum	141	4
5	Bob	151	5
6	Bob	161	6
7	Daniel	NULL	7
8	Emily	181	8
9	Daniel	191	9

Code

Using the useful function STRING_AGG(), we can concatenate rows to a single row: SELECT [Name], STRING_AGG([Code],'+') AS 'Codes', sum([Price]) AS 'TPrice' FROM #myTable GROUP BY [Name]

Result

This produces the following table:

	Name	Codes	TPrice
1	Alex	111+131	4
2	Bob	121+151+161	13
3	Callum	141	4
4	Daniel	191	16
5	Emily	181	8

Unconcatenate rows

Setup

Let's say I have the following table: #myTable

	Name	Codes
1	Alex	111+131
2	Bob	121+151+161
3	Callum	141
4	Daniel	NULL
5	Emily	181
6	Frank

Code - CROSS APPLY

Using the useful function STRING_SPLIT() from before, we can unconcatenate rows to multiple rows: SELECT [Name], value AS [Code] FROM #myTable CROSS APPLY STRING_SPLIT([Codes],'+');

Result

This produces the following table:

	Name	Code
1	Alex	111
2	Alex	131
3	Bob	121
4	Bob	151
5	Bob	161
6	Callum	141
7	Emily	181
8	Frank

Code - OUTER APPLY

Using the useful function STRING_SPLIT() from before, we can unconcatenate rows to multiple rows: SELECT [Name], value AS [Code] FROM #myTable OUTER APPLY STRING_SPLIT([Codes],'+');

Result

This produces the following table:

	Name	Code
1	Alex	111
2	Alex	131
3	Bob	121
4	Bob	151
5	Bob	161
6	Callum	141
7	Daniel	NULL
8	Emily	181
9	Frank

CROSS APPLY vs OUTER APPLY

CROSS APPLY is like an inner join, only for rows that are non-null on both "tables".
OUTER APPLY is like left join, for all rows on left table.

Sources

• learn.microsoft.com
• mssqltips.com