CODEWITHSUNDEEP
sqlt-sql
IanW
IanW

Reputation: 13

Nesting a WHILE loop within a SELECT

I'm trying to extract some data out of an EDMS document management system using T-SQL. Basically I need to create a report that lists all the documents on the system and the file path to each one of them. The "docs" table has one row for each document held with docid as the key. Here's an example:

docid size ext
10001 1 jpg
10002 1 png
10003 1 tif
10004 3 tif

The filename of the associated doc is normally the docid followed by the extension, however because a document in the form of a tif scan can be across several individual files a suffix is added for tif files consisting of an underscore followed by the part number (the size column indicates how many parts exist for each scan.) So for the above example I would need to output the following:

docid filepath
10001 c:\documents\10001.jpg
10002 c:\documents\10002.png
10003 c:\documents\10003_1.tif
10004 c:\documents\10004_1.tif
10004 c:\documents\10004_2.tif
10004 c:\documents\10004_3.tif

I figure it needs a WHILE loop nested within a SELECT somehow, but can't figure out how to achieve this.

Upvotes: 1

Views: 69

Answers (2)

Thom A
Thom A

Reputation: 95949

Assuming that size can't be a value greater than 10, you could use a VALUES table construct to create the extra rows:

SELECT YT.docid,
       CONCAT(N'C:\documents\',YT.docid,CASE YT.ext WHEN 'tif' THEN CONCAT(N'_',V.I) END,N'.',YT.ext) AS FilePath
FROM dbo.YourTable YT
     JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))V(I) ON YT.Size >= V.I;

If you could have much larger values, then you could instead use an inline tally, or even an existing Tally Table you have on your instance.

Worth noting, the reason I use a Tally is because they are significantly faster than an rCTE. Even for a small amount of rows there are performance benefits to using the set based method of a Tally over the iterative method of the rCTE.

It seems that the OP could have large numbers. As I mentioned, therefore, use an inline Tally, or a Tally Table you have on your instance. An inline tally would look like this:

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP (SELECT MAX(size) FROM dbo.YourTable)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3) --Up to 1000. Add more cross joins for more rows
SELECT YT.docid,
       CONCAT(N'C:\documents\',YT.docid,CASE YT.ext WHEN 'tif' THEN CONCAT(N'_',T.I) END,N'.',YT.ext) AS FilePath
FROM dbo.YourTable YT
     JOIN Tally T ON YT.Size >= T.I

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270823

You can do what you want using a recursive CTE:

with files as (
      select docid, ext, 1 as cnt, size
      from documents
      union all
      select docid, ext, cnt + 1, size
      from files
      where cnt < size
     )
select f.docid, f.ext, f.cnt,
       concat('c:\documents\', docid,
              (case when ext = 'tif' 
                    then concat('_', cnt)
                    else ''
               end), '.', ext)
from files f;

Here is a db<>fiddle.

Upvotes: 1

Related Questions