如何结合WITH子句与字符串拼接生成带表头的有序支付数据CSV

要实现自定义表头固定在顶部，同时支付数据按合同号（CONTRATO）和到期日（VENCIMENTO）排序，你可以通过添加排序优先级字段的方式解决UNION ALL与ORDER BY的冲突问题，具体修改如下：

WITH LOTE_GRAFICA AS
(SELECT MAX(FIN.NUMCONTRATO) AS CONTRATO, MAX(FIN.DTVENC) AS VENCIMENTO,
        TRIM(COALESCE(MAX(CON.CPF), '')) + '|' + 
        TRIM(COALESCE(MAX(CON.NOMEPARC), '')) + '|' + 
        TRIM(COALESCE(MAX(ENDER.TIPO), '')) + ' ' + 
        TRIM(COALESCE(MAX(CON.NOMEEND), '')) + '|' + 
        TRIM(COALESCE(MAX(CON.NUMEND), '')) + '|' + 
        TRIM(COALESCE(MAX(PAR.COMPLEMENTO), '')) + '|' + 
        TRIM(COALESCE(MAX(CON.NOMEBAI), '')) + '|' + 
        TRIM(COALESCE(MAX(CON.NOMECID), '')) + '|' + 
        TRIM(COALESCE(MAX(CON.UF), '')) + '|' + 
        TRIM(COALESCE(MAX(CON.CEP), '')) + '|' + 
        TRIM(CAST(REPLACE(MAX(FIN.VLRDESDOB), '.', '')AS varchar(10))) + '|' + 
        TRIM(CONVERT(varchar, FIN.DTVENC, 3)) + '|' + 
        TRIM(MAX(FIN.NOSSONUM)) + '|' + 
        TRIM(CAST(MAX(FIN.NUFIN) AS varchar(20))) + '|' + 
        TRIM(MAX(FIN.LINHADIGITAVEL)) + '|' + 
        TRIM(MAX(FIN.CODIGOBARRA)) + '|' + 
        TRIM(CAST(MAX(FIN.NUMCONTRATO) AS varchar(30))) + '|' + 
        TRIM(MAX(CON.PLANO)) + '|' + 
        TRIM(MAX(FIN.DESDOBRAMENTO)) AS CONTEUDO
FROM SANKHYA.AD_VGFREAJCON REAJ
INNER JOIN SANKHYA.TGFFIN FIN ON (FIN.NUMCONTRATO = REAJ.NUMCONTRATO)
INNER JOIN SANKHYA.TGFPAR PAR ON (FIN.CODPARC = PAR.CODPARC)
INNER JOIN SANKHYA.PREVENIR_APP_CONTRATO CON ON (PAR.CODPARC = CON.CODPARC)
INNER JOIN SANKHYA.TSIEND  ENDER ON (PAR.CODEND = ENDER.CODEND)
INNER JOIN SANKHYA.TCSCON STAT ON (STAT.NUMCONTRATO = CON.CONTRATO)
WHERE
REAJ.LOTEREAJ = 188
AND REAJ.FATURADO = 'S'
AND FIN.CODBCO = 237
AND FIN.DTVENC > GETDATE()+20
AND FIN.DTVENC < GETDATE()+400
AND FIN.NOSSONUM IS NOT NULL
AND FIN.VLRDESDOB > 1
AND FIN.DHBAIXA IS NULL
AND FIN.RECDESP = 1
AND FIN.NURENEG IS NULL
AND FIN.AD_ADESAO IS NULL
AND PAR.CGC_CPF IS NOT NULL
AND STAT.AD_STATUS = 'ATIVO'
GROUP BY CON.CONTRATO, FIN.DTVENC)

-- 添加排序优先级字段，表头行优先级为0，数据行优先级为1
SELECT 
    '04.333.253/0001-38' + '|' + 'PREVENIRASSISTENCIAL LTDA' + '|' + 'Av. Brasilia 1165, Loja 01 - Duquesa I - Sao Benedito - Santa Luzia - MG' + '|' + '237-2' + '|' + '3435' + '|' + '63184' + '|' + '1' + '|' + TRIM(CONVERT(varchar, GETDATE(), 3)) + '|' + TRIM(CONVERT(varchar, GETDATE(), 3)) + '|' + 'N' + '|' + 'DS' + '|' + '2' + '|' + 'R$' + '|' + '33170-000' AS ' ',
    0 AS SORT_ORDER
UNION ALL
SELECT 
    CONTEUDO AS ' ',
    1 AS SORT_ORDER,
    CONTRATO,
    VENCIMENTO
FROM LOTE_GRAFICA 
-- 先按优先级排序（表头在前），再按合同号和到期日排序数据
ORDER BY SORT_ORDER, CONTRATO, VENCIMENTO

关键修改说明 #

• 添加排序优先级字段：给表头行设置SORT_ORDER = 0，数据行设置SORT_ORDER = 1，确保表头始终排在结果集的最顶部。
• 调整ORDER BY逻辑：先按SORT_ORDER排序，再按CONTRATO和VENCIMENTO对数据行进行排序，既保证表头位置，又实现数据的有序性。
• 保留原业务逻辑：CTE部分的查询逻辑完全不变，仅修改最终的联合查询和排序规则，不影响原有数据的生成逻辑。

内容的提问来源于stack exchange，提问作者Pedro Leite