Splitting column to as many columns as needed by delimiter

[kariseightytwo]

Hello, I'm using what I believe to be an older version of SQL, and I'm having a difficult time achieving the results I'm looking for. The column I need split is called "Fixed Distribution List", or [MI_CONDALRT].[MI_CONDALRT_FXD_DIST_LIST_C], and the delimiter is "|". I'd like it to be split into as many new columns as needed. The most would probably be 10, but I suppose it could be more.

Here is the SQL query providing just an open with the un-split values:

SELECT [MI_CONDALRT].[MI_CONDALRT_ACTIVE_F] "Active?", [MI_CONDALRT].[MI_CONDALRT_ALERT_ID_C] "Alert ID", [MI_CONDALRT].[MI_CONDALRT_ALERT_OWNER_C] "Alert Owner", [MI_CONDALRT].[MI_CONDALRT_FXD_DIST_LIST_C] "Fixed Distribution List"
FROM [MI_CONDALRT]
WHERE ([MI_CONDALRT].[MI_CONDALRT_ACTIVE_F] = 'Y' AND [MI_CONDALRT].[MI_CONDALRT_ALERT_ID_C] LIKE '%incomplete rec%')

[robert_volk]

We'll need to see some sample data, plus example results you'd want to see from that data. Preferably as INSERT statements like:

CREATE TABLE test(test_column varchar(100) not null);
INSERT test(test_column) VALUES('First|Second|Third')
,('Fourth|Fifth|Sixth|Seventh')

Regarding "split[ting] into as many new columns as needed", that will be tricky to do in SQL, as it's intended to have explicitly specified column names, not dynamic ones. You can split this into a dynamic number of rows, and then use a PIVOT or other cross-tab technique to create column names and then put the relevant values in them

There's some techniques listed in this thread that will give you some ideas:

Transforming string to rows (HEEELP!)

Please help. The required code is beyond me. I need to transform data from a string into rows as below. @MyTable1 is how the data looks now and @MyTable2 is how I would like it to look. I am using SQL2012. -- Raw Data DECLARE @MyTable1 TABLE (CustomerId INT, InvoiceData VARCHAR(1000)) INSERT INTO @MyTable1 SELECT 1, '2022/01/31;INV001;125.50;2022/02/28;INV002;86.25' UNION SELECT 2, '2022/01/20;INV023;726.26;2022/02/14;INV043;200.50;2022/03/17;INV067;453.85' SELECT * FROM @…

Until there's some examples to work with though, I can't say if that will work for you or not.

[ScottPletcher]

If you're on SQL2016+ (I think), you can use the built-in STRING_SPLIT function.

If not, Google dbo.DelimitedSplit8K. Get the source code and create than function to do the split.

[kariseightytwo]

Hi again. Let me say thanks for your suggestions, though this all seems way over my head. I suppose I don't need an indefinite number of columns, but if it could be separated into 5 columns to start I'm sure I could expand myself after.
Here's a tiny screenshot of what the current data looks like:

It's obviously not much to go by, but given sensitivity concerns I can't really paste the data.
I guess I'll try to describe the desired outcome here:

If Fixed Distribution List looks like this,
john.doe@email .com | Jane Smith <janesmith@email .com> | Mike.smith@email .com |

I would like to see three columns:
Fixed Distribution List.1‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎Fixed Distribution List.2‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‎‏‏‎ ‎‏‏‎ ‏‏‎ ‎‏‏‎ ‎‏‏‎ ‏‏‎ ‎‏‏‎ ‎‏‏‎ ‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎Fixed Distribution List.3
john.doe@email .com‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎Jane Smith <janesmith@email .com>‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎‏‏‎ ‎Mike.smith@email .com

[kariseightytwo]

You know, let's just forget I asked. I'm going to export the data for them and split in Power Query. Much quicker at this point. Once again, thank you for your help!