Eugene Elutin (5/8/2013)JPG is not very good idea for posting query plan. You better save it as XML. You need to post complete query if you want relevant help. Also, posting DDL of involved database objects would be helpful.
Apologies for not doing this in my first post, thought it would simplify matters to just ask about a table joining on itself but I should have known better. The full query plan with obfuscated names is attached as a .sqlplan file (xml wasn't a permitted type but the xml is in the file).
Here is the complete query. I've moved the a-related filters from the JOIN clause to the WHERE clause per your suggestion:
SELECT
a.CompanyIdentifier
,COALESCE(a.Data,a1.Data)
,COALESCE(a2.Data,a3.Data)
,a4.Data
FROM SourceTable a
LEFT JOIN SourceTable a1
ON
a.CompanyIdentifier = a1.CompanyIdentifier
AND a1.FieldIDNumber = 2
AND LEFT(a1.CompanyIdentifier, 5) != 'EXCLU'
AND a1.IndicatorRecordContainsAlphanumericNoteRatherThanData = ''
LEFT JOIN SourceTable a2
ON
a.CompanyIdentifier = a2.CompanyIdentifier
AND a2.FieldIDNumber = 3
AND LEFT(a2.CompanyIdentifier, 5) != 'EXCLU'
AND a2.IndicatorRecordContainsAlphanumericNoteRatherThanData = ''
LEFT JOIN SourceTable a3
ON
a.CompanyIdentifier = a3.CompanyIdentifier
AND a3.FieldIDNumber = 4
AND LEFT(a3.CompanyIdentifier, 5) != 'EXCLU'
AND a3.IndicatorRecordContainsAlphanumericNoteRatherThanData = ''
LEFT JOIN SourceTable a4
ON
a.CompanyIdentifier = a4.CompanyIdentifier
AND a4.FieldIDNumber = 5
AND LEFT(a4.CompanyIdentifier, 5) != 'EXCLU'
AND a4.IndicatorRecordContainsAlphanumericNoteRatherThanData = ''
WHERE
a.FieldIDNumber = 1
AND LEFT(a.CompanyIdentifier, 5) != 'EXCLU'
AND a.IndicatorRecordContainsAlphanumericNoteRatherThanData = ''
GROUP BY
a.CompanyIdentifier
,a.Data
,a1.Data
,a2.Data
,a3.Data
,a4.Data
ORDER BY
a.CompanyIdentifier
,a.Data
,a1.Data
,a2.Data
,a3.Data
,a4.Data
ASC
Here is the DDL for the table and its primary key:
CREATE TABLE SourceTable
(
CompanyIdentifier [varchar](9) NOT NULL,
PKColumn1NotPartOfQuery [int] NOT NULL,
PKColumn2NotPartOfQuery [varchar](1) NOT NULL,
PKColumn3NotPartOfQuery [smallint] NOT NULL,
FieldIDNumber [decimal](5, 0) NOT NULL,
IndicatorRecordContainsAlphanumericNoteRatherThanData [varchar](4) NOT NULL,
PKColumn4NotPartOfQuery [smallint] NOT NULL,
Data[varchar](8000) NULL,
PRIMARY KEY CLUSTERED
(
CompanyIdentifier ASC,
PKColumn1NotPartOfQuery ASC,
PKColumn2NotPartOfQuery ASC,
PKColumn3NotPartOfQuery ASC,
FieldIDNumber ASC,
IndicatorRecordContainsAlphanumericNoteRatherThanData ASC,
PKColumn4NotPartOfQuery ASC
)
WITH
(
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
)
ON [PRIMARY]
) ON [PRIMARY]