TSQL 将多行合并为一行 [英] TSQL Combining Multiple Rows Into One Row

问题描述

我正在尝试创建一个查询来合并以下信息:

I am trying to create a query to combine the following information:

FileID  ErrorCode  ErrorDesc             ErrorCount
  1         4      Bad File Name             3
  2         6      Bad File Code            56
  3         4      Bad File Name             2
  3        12      Line Length Invalid       3
  3        17      Missing Req Fields      150

我想根据 FileID 组合所有行，以便给定 FileID 的所有信息将显示在同一行中，如下所示:

I want to combine all rows based on the FileID so that all information for a given FileID would appear on the same line as the following:

1  4  Bad File Name     3
2  6  Bad File Code    56
3  4  Bad File Name     2     12  Line Length Invalid  3     17 Missing Req Fields  150

我运行它的问题是每个给定文件都会有未知数量的错误.它可能有 1-50 个错误，我想将所有这些信息合并在一行中.我不确定这是否可能，或者是否有另一种方式来看待这个问题.我的最终目标是最终根据这些数据创建一份报告.谢谢！

The problem I am running it to there will be an unknown amount of errors per given file. It could have 1-50 errors and I would like to combine all of that information under one row. I am not sure if this is possible or if there is another way to look at this issue. My end goal is to end up creating a report off of this data. Thanks!

推荐答案

比 Mikael 的稍微多一点 - 主要区别在于这里维护列(但是每个错误代码的列不是第一个排队").

A little more involved than Mikael's - the main difference is that columns are maintained here (however the columns for each error code other than the first "line up").

设置:

CREATE TABLE dbo.t
(
    FileID INT,
    ErrorCode INT,
    ErrorDesc VARCHAR(255),
    ErrorCount INT
);

INSERT dbo.t VALUES
(1,4,'Bad File Name',3),
(2,6,'Bad File Code',56),
(3,4,'Bad File Name',2),
(3,12,'Line Length Invalid',3),
(3,17,'Missing Req Fields',150);

代码:

DECLARE 
    @sql0 NVARCHAR(MAX) = N'',
    @sql1 NVARCHAR(MAX) = N'',
    @sql2 NVARCHAR(MAX) = N'',
    @minC INT;

SELECT @minC = MIN(ErrorCode) FROM dbo.t;

SELECT @sql1 += REPLACE(',x$.ErrorCode AS Code$,
    x$.ErrorDesc AS Desc$,x$.ErrorCount AS Count$', 
    '$', CONVERT(VARCHAR(12), ErrorCode))
    FROM dbo.t WHERE ErrorCode > @minC GROUP BY ErrorCode ORDER BY ErrorCode;

SELECT @sql2 += REPLACE(' 
    LEFT OUTER JOIN x AS x$ ON z.FileID = x$.FileID 
    AND x$.ErrorCode = $
    AND x$.ErrorCode > z.ErrorCode', '$', CONVERT(VARCHAR(12), ErrorCode))
    FROM dbo.t WHERE ErrorCode > @minC GROUP BY ErrorCode ORDER BY ErrorCode;

SET @sql0 = ';WITH y AS (
    SELECT FileID, ErrorCode, ErrorDesc, ErrorCount,
        rn = ROW_NUMBER() OVER (PARTITION BY FileID ORDER BY ErrorCode)
    FROM dbo.t
),
z AS ( SELECT FileID, ErrorCode, ErrorDesc, ErrorCount FROM y WHERE rn = 1 ),
x AS ( SELECT FileID, ErrorCode, ErrorDesc, ErrorCount FROM y WHERE rn > 1 )
SELECT z.*' + @sql1 + ' FROM z
' + @sql2;

-- PRINT @sql0; -- to see what has been crafted    
EXEC sp_executesql @sql0;
GO

清理:

DROP TABLE dbo.t;
GO

这篇关于TSQL 将多行合并为一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！