Okay, this should be simple.
I have a table with data in one column. I would like to split the data into two columns but carry down a value.
So my table:
CREATE TABLE DListx(InitialVal varchar(500), IDX int NOT NULL);
INSERT INTO DListx (InitialVal, IDX) VALUES
('Directory of \\PRODSERV\C$\Management', 4)
('04/01/2009 02:23 PM 144 exclude.txt', 11)
('Directory of \\PRODSERV\C$\Management\dlm', 26)
('09/30/2006 08:20 AM 20,992 01018440.xlt', 30)
('09/08/2006 01:44 PM 3,330 09-07-06 EIS EXCEPTION REPORT.xls.pgp', 31)
('02/03/2009 04:30 PM 12,288 1.09 Dialer .xls', 32)
('02/02/2009 02:45 PM 1,027,072 2009 01 accst worked.xls', 33);
Desired output:
IDX Folder Filex
11 Directory of \\PRODSERV\C$\Management 04/01/2009 02:23 PM 144 exclude.txt
30 Directory of \\PRODSERV\C$\Management\dlm 09/30/2006 08:20 AM 20,992 01018440.xlt
31 Directory of \\PRODSERV\C$\Management\dlm 09/08/2006 01:44 PM 3,330 09-07-06 EIS EXCEPTION REPORT.xls.pgp
32 Directory of \\PRODSERV\C$\Management\dlm 02/03/2009 04:30 PM 12,288 1.09 Dialer .xls
33 Directory of \\PRODSERV\C$\Management\dlm 02/02/2009 02:45 PM 1,027,072 2009 01 accst worked.xls
Getting the data I want to split is simple as the value starts the same
WHERE LEFT(InitialVal, 9) = 'Directory'
I know there is a way to populate a column based on the IDX but I am not thinking correctly about it so cannot remember.
Thank you
P.S. How do I notate text? above I used the code </>.