lib.categories.typoscript

# Show possible errors
config.contentObjectExceptionHandler = 0

lib.categories = CONTENT
lib.categories {
  table = sys_category
  select {
    # Adapt to your needs
    pidInList = root
    # Set a recursive level if needed
    # recursive = 2
    
    leftjoin = sys_category_record_mm ON sys_category.uid = sys_category_record_mm.uid_local
    where.dataWrap = sys_category_record_mm.tablenames = 'sys_file_metadata' AND sys_category_record_mm.fieldname = 'categories' AND sys_category_record_mm.uid_foreign = {CURRENT:1}    
  }
  
  # Render categories: cat1, cat2, cat3, 
  renderObj = TEXT
  renderObj {
    field = title
    noTrimWrap = ||, |
  }
  
  # remove last space because of noTrimWrap above
  stdWrap.trim = 1
  # remove last comma because of noTrimWrap above
  stdWrap.substring = 0, -1
}

config.contentObjectExceptionHandler = 0
# Show categories of table tt_content
tt_content.text.dataProcessing {
  10 = TYPO3\CMS\Frontend\DataProcessing\DatabaseQueryProcessor
  10 {
    table = sys_category
    selectFields = sys_category.uid, sys_category.title
    pidInList = root
    join = sys_category_record_mm ON sys_category.uid = sys_category_record_mm.uid_local
    where.dataWrap = sys_category_record_mm.tablenames = 'tt_content' AND sys_category_record_mm.fieldname = 'categories' AND sys_category_record_mm.uid_foreign = {FIELD:uid}
    as = contentCategories
  }
}

# Show categories of table pages
page.10.dataProcessing {
  10 = TYPO3\CMS\Frontend\DataProcessing\DatabaseQueryProcessor
  10 {
    table = sys_category
    selectFields = sys_category.uid, sys_category.title
    pidInList = root
    join = sys_category_record_mm ON sys_category.uid = sys_category_record_mm.uid_local
    where.dataWrap = sys_category_record_mm.tablenames = 'pages' AND sys_category_record_mm.fieldname = 'categories' AND sys_category_record_mm.uid_foreign = {FIELD:uid}
    as = pageCategories
  }
}

# Within the Fluid Template use. And YES, use it with the prepended dot in data attribute:
<f:cObject typoscriptObjectPath="lib.categories" data="{file.originalFile.metaData.}" currentValueKey="uid" table="sys_file_metadata" />

If you use pidInList = 0 categories get selected from any page, not just the root page

@linawolf If I set pidInList = 0 the pidclause will be removed from query. That's correct. But in that case sys_category.uid = 0 will be added which results in no categories anymore.

@froemken it seems like this only works when uidInList es set, unfortunary: https://docs.typo3.org/m/typo3/reference-typoscript/11.5/en-us/Functions/Select.html#pidinlist

Be careful with recursive in large websites! With recursive = 99 (as posted on StackOverflow) the entire pagetree is queried page by page for its subpages to build the list of IDs. On a site with 10T pages this caused (uncached) almost 25,000 SQL queries.