split data delimited by character

Question

I really would appreciate someone's help I`m a newbie in all of this SQL-SSIS stuff I have the following table in SQL:

And I need to create a table with columns that contain the AreaPath column and parsed by the delimiter \ Meaning my final goal is this:

Couple of important things:

1. The original table is constantly changing (every 10 minutes)
2. I have SQL 2014
3. I can use SSIS to do so (I prefer to, than SQL)

Any help would be appreciated Thanks!

Answer 1

If you use the string delimit function from this link and use PIVOT

Then the following works

DECLARE @Delim NVARCHAR(32) = '\';

DECLARE @t TABLE ( AreaSK INT, AreaName NVARCHAR(100), AreaPath NVARCHAR(100))
INSERT INTO @t
(AreaSK, AreaName, AreaPath)
VALUES
(12188, 'Kryptonite', '\Kryptonite'),
(12191, 'SL_A', '\Kryptonite\SL_A'),
(12192, 'P_Bender', '\Kryptonite\SL_A\ART_APL\PG_Apollo\P_Bender'),
(12194, 'PG_Edison', '\Kryptonite\SL_A\ART_APL\PG_Apollo\P_Bender\test\PG_Edison')

SELECT PVT.AreaSK
     , PVT.AreaName
     , PVT.TeamProject
     , PVT.AL
     , PVT.ART
     , PVT.PG
     , PVT.P
     , PVT.T
FROM
(
    SELECT
          T.AreaSK
        , T.AreaName
        , CASE
            WHEN rn = 1 THEN 'TeamProject'
            WHEN rn = 2 THEN 'AL'
            WHEN rn = 3 THEN 'ART'
            WHEN rn = 4 THEN 'PG'
            WHEN rn = 5 THEN 'P'
            WHEN rn = 6 THEN 'T'
          END 'Headings'
        , [Value]
    FROM 
    (
        SELECT T.AreaSK
             , T.AreaName
             , T.AreaPath
             , AreaPath2 = RIGHT(T.AreaPath, LEN(T.AreaPath) - 1) --remove first '\'
        FROM @t T
    )   T
    CROSS APPLY
        dbo.FN_SplitString_AB(T.AreaPath2, @Delim)
) T
PIVOT
(
    MAX(Value)
    FOR Headings IN ([T],[P],[PG],[ART],[AL],[TeamProject])
) PVT

Output