Get file name with two "." and roll up by Directory?

Question

I have a table that looks like this:

And I need to split the filenames from extensions and put them both into separate columns.

it should look like this:

Then I need to roll them all up by directory with another field containing comma separated list of all file extension in that folder.

EG: Here is what the end product should be:

Here is what I have so far:

 select 
    Length,
    (case when Name like '%.%'
      then (left((Name), charindex('.', (Name)) - 1))
      else ''
 end) as FileName
,(case when Name like '%.%'
      then reverse(left(reverse(Name), charindex('.', reverse(Name)) -                        1))
      else ''
 end) as Extension
 ,Directory
 FROM   dbo.[SourceRetail-V1] 
 WHERE Mode not like 'd--%'
 order by Directory asc

Issues:

1. How do I get filenames with something like "FileName.MoreFileName.Txt"
   a. It should look like this "FileName.MoreFileName" but my code sees the period and then strips it to "FileName"
2. How do I roll up by directory yet still keep a running list of all file extension in the directory in another field?

Here is the source in text:

Mode    Length  Name    Directory
-a---   78497   BSAS.map.xml    Y:\Data\Retail\BQ\Maps\SAP
-a---   4329    T052.tf.sql Y:\Data\Retail\BQ\Maps\SAP
-a---   24268   T052.map.txt    Y:\Data\Retail\BQ\Maps\SAP
-a---   53837   PAYR.map.xml    Y:\Data\Retail\BQ\Maps\SAP
-a---   4321    LFB1.tf.xml Y:\Data\Retail\BQ\Maps\SAP
-a---   146089  BSAK.map.xml    Y:\Data\Retail\BQ\Maps\SAP
-a---   4322    LFA1.tf.xml Y:\Data\Retail\BQ\Maps\SAP
-a---   4325    LFC1.tf.xml Y:\Data\Retail\BQ\Maps\SAP

Intermediary Table:

Mode    Length  Name    Extension   Directory
-a---   78497   BSAS.map    xml Y:\Data\Retail\BQ\Maps\SAP
-a---   4329    T052.tf sql Y:\Data\Retail\BQ\Maps\SAP
-a---   24268   T052.map    txt Y:\Data\Retail\BQ\Maps\SAP
-a---   53837   PAYR.map    xml Y:\Data\Retail\BQ\Maps\SAP
-a---   4321    LFB1.tf xml Y:\Data\Retail\BQ\Maps\SAP
-a---   146089  BSAK.map    xml Y:\Data\Retail\BQ\Maps\SAP
-a---   4322    LFA1.tf xml Y:\Data\Retail\BQ\Maps\SAP
-a---   4325    LFC1.tf xml Y:\Data\Retail\BQ\Maps\SAP

End Product:

Mode    Length  Directory   Extensions
a----   319998  Y:\Data\Retail\BQ\Maps\SAP  xml,sql,txt

Answer 1

For the first query you can use

select substring([FileName], 0, len([FileName])- charindex('.', reverse([FileName]))+1)
from tablename

For second question you can use For XML Path/STUFF to concatenate all the extensions

Answer 2

Try using STUFF to do the extension list.

select distinct
    Directory,
    Ext = STUFF(( SELECT distinct 
                   case when t2.Name like '%.%' 
                      then ' | '+ reverse(left(reverse(t2.Name), charindex('.',reverse(t2.Name))-1))  
                      else '' 
                    end
                FROM @T t2
                WHERE t2.Directory = t1.Directory 
                ORDER BY 1
                FOR XML PATH('')), 1, 3, '')
from @T t1

Answer 3

I only pasted two records into the the demonstrative table variable, but this should do.

Declare @YourTable table (Mode varchar(50),Length int,Name varchar(100),Directory varchar(250))
Insert into @YourTable values
('-a---',78497,'BSAS.map.xml','Y:\Data\Retail\BQ\Maps\SAP'),
('-a---',4329 ,'T052.tf.sql' ,'Y:\Data\Retail\BQ\Maps\SAP'),
('-a---',4329 ,'NoExtension' ,'Y:\Data\Retail\BQ\Maps\SAP')


Select A.Mode
      ,Length = sum(A.Length)
      ,A.Directory
      ,Extensions = max(B.Extensions)
 From @YourTable A
 Cross Apply (
               Select Extensions=Stuff((
                Select Distinct ',' + case when CharIndex('.',Name)=0 then 'None' else Right(Name,CharIndex('.',Reverse(Name))-1) end
                 From  @YourTable 
                 Where Directory=A.Directory
                 Order By 1  
                 For XML Path ('')),1,1,'')
              ) B
 Group By A.Mode,A.Directory

Returns

Mode    Length  Directory                   Extensions
-a---   87155   Y:\Data\Retail\BQ\Maps\SAP  None,sql,xml