VinnyGuitara VinnyGuitara - 1 year ago 64
SQL Question

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

I have a table that looks like this:

enter image description here

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

it should look like this:
enter image description here

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

Here is what the end product should be:

Here is what I have so far:

Here is what I have so far:

(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
FROM dbo.[SourceRetail-V1]
WHERE Mode not like 'd--%'
order by Directory asc


  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 Y:\Data\Retail\BQ\Maps\SAP
-a--- 4329 Y:\Data\Retail\BQ\Maps\SAP
-a--- 24268 Y:\Data\Retail\BQ\Maps\SAP
-a--- 53837 Y:\Data\Retail\BQ\Maps\SAP
-a--- 4321 Y:\Data\Retail\BQ\Maps\SAP
-a--- 146089 Y:\Data\Retail\BQ\Maps\SAP
-a--- 4322 Y:\Data\Retail\BQ\Maps\SAP
-a--- 4325 Y:\Data\Retail\BQ\Maps\SAP

Intermediary Table:

Mode Length Name Extension Directory
-a--- 78497 xml Y:\Data\Retail\BQ\Maps\SAP
-a--- 4329 sql Y:\Data\Retail\BQ\Maps\SAP
-a--- 24268 txt Y:\Data\Retail\BQ\Maps\SAP
-a--- 53837 xml Y:\Data\Retail\BQ\Maps\SAP
-a--- 4321 xml Y:\Data\Retail\BQ\Maps\SAP
-a--- 146089 xml Y:\Data\Retail\BQ\Maps\SAP
-a--- 4322 xml Y:\Data\Retail\BQ\Maps\SAP
-a--- 4325 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 Source

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---',4329 ,'' ,'Y:\Data\Retail\BQ\Maps\SAP')

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


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