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"
WHERE ([MI_CONDALRT].[MI_CONDALRT_ACTIVE_F] = 'Y' AND [MI_CONDALRT].[MI_CONDALRT_ALERT_ID_C] LIKE '%incomplete rec%')
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')
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:
Until there's some examples to work with though, I can't say if that will work for you or not.
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: