Populate column from same table

Transact-SQL
1

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 </>.

2 
select    IDX, Folder, InitialVal
from    DListx l
    cross apply
    (
        select    top 1 Folder = InitialVal
        from    DListx x
        where    InitialVal    LIKE 'Directory%'
        and    x.IDX    < l.IDX
        order by x.IDX desc
    ) f
where    InitialVal    NOT LIKE 'Directory%'
1 Like
3

Thank you, @khtan !
I knew it involved idx < idx but could not remember how. I had also forgotten it was a cross apply, I was trying a regular join. :neutral_face:

4

Hi

Another way to get the same result

SELECT b.idx,
       a.initialval,
       b.initialval
FROM   (SELECT initialval,
               idx,
               Lead(initialval) OVER ( ORDER BY idx) AS leadInitialVal,
               Isnull(Lead(idx) OVER ( ORDER BY idx),(SELECT Max(idx) FROM   dlistx)) AS leadidx
        FROM   dlistx
        WHERE  initialval LIKE 'Dir%') AS a
       INNER JOIN
       (SELECT *
        FROM   dlistx
        WHERE  initialval NOT LIKE 'Dir%') AS b
       ON b.idx BETWEEN a.idx AND a.leadidx;
5

Thank you for your reply @hhrr

Notice that I am in 2008R2 so no LEAD, but if someone is looking up information it is valuable.