Combining 2 records into 1

I have the follwing tables which contain error records and error reasons.
There are two erros entries for every error reason.
DDL
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[INPUT_ERRORS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[INPUT_ERRORS]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[INPUT_ERRORS_REASON]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[INPUT_ERRORS_REASON]
GO
CREATE TABLE [dbo].[INPUT_ERRORS] (
[Licence] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ReportNumber] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ErrorItemErrorItemType] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ErrorItemreference] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[AUtoRef] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[INPUT_ERRORS_REASON] (
[Licence] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ReportNumber] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ErrorItemreference] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ErrorMessageMessageLine] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AUtoRef] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
SET NOCOUNT ON
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217523','16781','ORIGINAL RECORD','19069',523)
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217523','16781','RETURNED RECORD','19069',524)
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','ORIGINAL RECORD','7',537)
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','AMENDED RECORD','7',538)
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','ORIGINAL RECORD','CONTRA',539)
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','AMENDED
RECORD',' BACS',540)SET NOCOUNT ON
SET NOCOUNT OFF
SET NOCOUNT ON
INSERT INTO [INPUT_ERRORS_REASON]
([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217523','16781','19069','RECI
PIENT''S SORT CODE IS INVALID',23
6)
INSERT INTO [INPUT_ERRORS_REASON]
([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217993','25194','7','YOUR ACCOUNT DETAILS ARE INVALID',243)
INSERT INTO [INPUT_ERRORS_REASON]
([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217993','25194','CONTRA','YOU
R
CONTRA ACCOUNT DETAILS (FIELDS A/B/C AND/OR E/F) ARE INVALID',244)
SET NOCOUNT OFF
i would like to get this sresult returned for easy reading in a report
Licence ReportNumber ErrorItemErrorItemType1 ErrorItemreference1
ErrorItemErrorItemType2 ErrorItemreference2 ErrorMessageMessageLi
ne
'217523' '16781' 'ORIGINAL RECORD' '19069'
'RETURNED RECORD' '19069' 'RECIPIENT''S SORT CODE IS
INVALID'
'217993' '25194' 'ORIGINAL RECORD' '7'
'AMENDED RECORD' '7' 'YOUR ACCOUNT DETAILS ARE
INVALID'
'217993' '25194' 'ORIGINAL RECORD' 'CONTRA'
'AMENDED RECORD' ' BACS' 'CONTRA','YOUR CONTRA ACCOUNT
DETAILS (FIELDS A/B/C AND/OR E/F) ARE INVALID'Peter Newman wrote:
> I have the follwing tables which contain error records and error reasons.
> There are two erros entries for every error reason.
> DDL
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[INPUT_ERRORS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[INPUT_ERRORS]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[INPUT_ERRORS_REASON]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[INPUT_ERRORS_REASON]
> GO
> CREATE TABLE [dbo].[INPUT_ERRORS] (
> [Licence] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ReportNumber] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ErrorItemErrorItemType] [varchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ErrorItemreference] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [AUtoRef] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[INPUT_ERRORS_REASON] (
> [Licence] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ReportNumber] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ErrorItemreference] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [ErrorMessageMessageLine] [varchar] (255) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AUtoRef] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
>
> SET NOCOUNT ON
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217523','16781','ORIGINAL RECORD','19069',523)
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217523','16781','RETURNED RECORD','19069',524)
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','ORIGINAL RECORD','7',537)
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','AMENDED RECORD','7',538)
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','ORIGINAL RECORD','CONTRA',539)
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','AMENDED
> RECORD',' BACS',540)SET NOCOUNT ON
> SET NOCOUNT OFF
> SET NOCOUNT ON
> INSERT INTO [INPUT_ERRORS_REASON]
> ([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217523','16781','19069','RECI
PIENT''S SORT CODE IS INVALID',
236)
> INSERT INTO [INPUT_ERRORS_REASON]
> ([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217993','25194','7','YOUR ACCOUNT DETAILS ARE INVALID',243)
> INSERT INTO [INPUT_ERRORS_REASON]
> ([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217993','25194','CONTRA','YOU
R
> CONTRA ACCOUNT DETAILS (FIELDS A/B/C AND/OR E/F) ARE INVALID',244)
> SET NOCOUNT OFF
> i would like to get this sresult returned for easy reading in a report
> Licence ReportNumber ErrorItemErrorItemType1 ErrorItemreference1
> ErrorItemErrorItemType2 ErrorItemreference2 ErrorMessageMessage
Line
> '217523' '16781' 'ORIGINAL RECORD' '19069'
> 'RETURNED RECORD' '19069' 'RECIPIENT''S SORT CODE IS
> INVALID'
> '217993' '25194' 'ORIGINAL RECORD' '7'
> 'AMENDED RECORD' '7' 'YOUR ACCOUNT DETAILS ARE
> INVALID'
> '217993' '25194' 'ORIGINAL RECORD' 'CONTRA'
> 'AMENDED RECORD' ' BACS' 'CONTRA','YOUR CONTRA ACCOUN
T
> DETAILS (FIELDS A/B/C AND/OR E/F) ARE INVALID'
Thanks for including the DDL. Unfortunately neither table has any keys!
Including an IDENTITY column means we can probably guess that that is a
key but an IDENTITY may not be enough to solve your problem. IDENTITY
should not be the only key of a table but you haven't shown us any
other so we can only guess. Here's one possibility:
SELECT licence, reportnumber,
LEFT(err1,50) AS erroritemtype1, RIGHT(err1,20) AS erroritemref1,
LEFT(err2,50) AS erroritemtype2, RIGHT(err2,20) AS erroritemref2
FROM
(SELECT licence, reportnumber,
MIN(CAST(erroritemerroritemtype AS CHAR(50))
+CAST(erroritemreference AS CHAR(20))) AS err1,
MAX(CAST(erroritemerroritemtype AS CHAR(50))
+CAST(erroritemreference AS CHAR(20))) AS err2
FROM input_errors
GROUP BY licence, reportnumber) AS T ;
David Portas
SQL Server MVP
--