Iterate through SELECT case query rows, append "comma" to every row except last-CodePudding

I have multiple absolute file paths that I have stored in the column "name" which is of the String Datatype, I have a switch case SELECT query which adjusts the output such that:

The filepaths are replaced by the _2 values (a new destination)
The values in the "name" column contains Uppercase and lowercase
I return the result(s) in the new destination that I specify via _2 but convert the filename portion as UPPERCASE
Finally, for every row - I concatenate a "," (comma) at the end of each row output

Legend:

a1=C:\Dir1\RESULT\filelist\filename.file
a2=E:\SomeOtherDir1\RESULT\x\y\z\filelist\FILENAME.FILE

b1=C:\Dir2\RESULT\filelist\filename.file
b2=E:\SomeOtherDir2\RESULT\1\2\3\filelist\FILENAME.FILE

... etc

I know what all the a2, b2 and c2 paths are.

The below block works as intended and I basically use REPLACE() with a combination of UPPER() to effectively uppercase the filename portion only.

SELECT
CASE
  WHEN name LIKE 'C:\Dir1\RESULT\filelist\%' -- this is a1%
THEN REPLACE (UPPER(name), 'C:\Dir1\RESULT\filelist\', -- this is a1
  'E:\SomeOtherDir1\RESULT\x\y\z\filelist\') || ''',' -- this is a2, returned with UPPERCASE filename and concat a comma

  WHEN name LIKE 'C:\Dir2\RESULT\filelist\%' -- this is b1%
THEN REPLACE (UPPER(name), 'C:\Dir1\RESULT\filelist\', -- this is b1
  'E:\SomeOtherDir2\RESULT\1\2\3\filelist\') || ''',' -- this is b2, returned with UPPERCASE filename and concat a comma

  WHEN name LIKE 'c1%'
THEN REPLACE (UPPER(name), 'c1',
  'c2') || ''','
-- etc
END AS "NEW_PATH"
FROM table ORDER BY 1;

How would I now make the above code add a comma for each row except the last row?

ie, if I have 50 rows, only append a comma for the first 49 rows..... retrospectively if I have 1025 rows, only append a comma for the first 1024 rows

I hope that is clear.

Many thanks!

CodePudding user response：

You can replace the strings without adding commas to any, and then use an analytic function and case expression to append a comma afterwards, to all but the last, using something like:

CASE WHEN ROW_NUMBER() OVER (ORDER BY new_path DESC) > 1 THEN ',' END

The row_number() assigns a sequential number based on the specified ordering; by using DESC the first row is assigned 50 or 1024 or whatever, which you don't really care about, but the last one is always assigned 1. That lets you apply the logic for everything except that row numbered 1, which is the last row - so that doesn't get a comma, but all the others do.

If you can use the original name for the ordering (i.e. the replacement doesn't change the order) you can do it as part of one level of query:

SELECT
CASE
  WHEN name LIKE 'C:\Dir1\RESULT\filelist\%'
  THEN REPLACE (UPPER(name),
    'C:\DIR1\RESULT\FILELIST\',
    'E:\SomeOtherDir1\RESULT\x\y\z\filelist\')

  WHEN name LIKE 'C:\Dir2\RESULT\filelist\%'
  THEN REPLACE (UPPER(name), 
    'C:\DIR2\RESULT\FILELIST\',
    'E:\SomeOtherDir2\RESULT\1\2\3\FILELIST\')
-- etc
  END
  || CASE WHEN ROW_NUMBER() OVER (ORDER BY name DESC) > 1 THEN ',' END
  AS new_path
FROM your_table ORDER BY 1;

or if the final order doesn't match the original order use a subquery:

SELECT new_path
  || CASE WHEN ROW_NUMBER() OVER (ORDER BY new_path DESC) > 1 THEN ',' END
  AS new_path
FROM (
  SELECT
  CASE
    WHEN name LIKE 'C:\Dir1\RESULT\filelist\%'
    THEN REPLACE (UPPER(name),
      'C:\DIR1\RESULT\FILELIST\',
      'E:\SomeOtherDir1\RESULT\x\y\z\filelist\')

    WHEN name LIKE 'C:\Dir2\RESULT\filelist\%'
    THEN REPLACE (UPPER(name), 
      'C:\DIR2\RESULT\FILELIST\',
      'E:\SomeOtherDir2\RESULT\1\2\3\FILELIST\')
  -- etc
    END AS new_path
  FROM your_table ORDER BY 1
)
ORDER BY new_path;

db<>fiddle