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

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

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!

回答1:

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").

Setup:

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);

Code:

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

Clean-up:

DROP TABLE dbo.t;
GO

回答2:

declare @T table (FileID int, ErrorCode int, ErrorDesc varchar(max), ErrorCount int)

insert into @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)

select FileID,
       (select cast(ErrorCode as varchar(10))+' '+ErrorDesc+' '+cast(ErrorCount as varchar(10))+' '
        from @T as T2
        where T1.FileID = T2.FileID
        for xml path(''), type).value('.', 'varchar(max)') 
from @T as T1
group by FileID