如何返回列名作为第一行的 T-SQL 查询 [英] How to return T-SQL query with column names as first row

问题描述

我正在编写一个 SSIS 包，以将数据从 SQL Server 2012 数据库输出到客户端的 .CSV 文件.要求是第一行是列名.下面是我为数据流任务中的源编写的查询.问题是，它总是将列名作为最后一行返回，而不是第一行.为什么?我如何实现这一目标?

I'm writing a SSIS package to output data from a SQL Server 2012 database to a .CSV file for a client. The requirement is that the first row be the column names. Below is the query I've written for the Source in the Data Flow Task. The problem is, it always returns the column names as the LAST row, not the first. Why? How do I achieve this?

DECLARE @Today AS DateTime= GETDATE()
DECLARE @NextPayrollDate AS DateTime

EXEC mobile.getNextPayrollDate @Today, @NextPayrollDate OUTPUT

;WITH LatestEligible (EmployeeID, LatestBillVerified) AS
(
    SELECT 
        EmployeeID, MAX(DateBillVerified) AS LatestBillVerified
    FROM 
        Inv_DataReimbursement
    GROUP BY 
        EmployeeID
)
SELECT
    'Edit Set' AS 'Edit Set', 
    'Employee No.' AS 'Employee No.'
FROM 
    LatestEligible

UNION

SELECT 
    NULL AS 'Edit Set',
    d.EmployeeID AS 'Employee No.'
FROM 
    LatestEligible d
INNER JOIN 
    Employee e ON d.EmployeeID = e.EmployeeID
INNER JOIN 
    Inv_DataReimbursement dr ON d.EmployeeID = dr.EmployeeID 
                             AND d.LatestBillVerified = dr.DateBillVerified
WHERE 
    (dr.MonthlyServiceEligible = 'true'
     OR (dr.MonthlyServiceEligible = 'false' 
         AND e.DateEnd IS NOT NULL 
         AND e.DateEnd > @NextPayrollDate))
    AND dr.ActualAmount > 0

推荐答案

我如何实现这一目标?

How do I achieve this?

不要通过 SQL 来实现.

Don't do it through SQL.

只需勾选 平面文件连接管理器并保持原始查询不变.

Just tick the Column names in the first data row box in the Flat File Connection Manager and leave the original query untouched.

然后将自动添加列标题，而无需合并这些额外的元数据(并可能将所有内容都转换为 SQL 端的字符串).

The column headers will then be added automatically without you needing to union this additional metadata (and potentially cast everything as a string on the SQL side).

这篇关于如何返回列名作为第一行的 T-SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！