Here's an example of what I'm talking about. Of course, I don't have access to the data so the code is completely untested but this will calculate the delimited aggregations just once for each TemplateID/SpecID combination in the PstProdTmplData table instead of 6 identical recalculations for each and every row of the InventTable.

While the following looks like a lot of code, it will allow your code to run with comparatively blazing performance as to the way it is currently structured. It's a very common method (pre-aggregate and pivot using a Cross Tab) to solve the problem of using an EAV table effectively.

WITH

ctePreAgg AS

(

--===== Preaggregate the semi-colon delimited "Data" for each TemplateID/SpecID combination

-- for performance. We'll pivot the data later

SELECT p1.TemplateID

, p1.SpecID --To be used as a join filter in another query.

, SpecIDs =

STUFF(

(

SELECT ' ; ' + p2.Data

FROM dbo.PstProdTmplData p2

WHERE p2.TemplateID = p1.TemplateID

FOR XML PATH('')

)

,1,3,'')

FROM dbo.PstProdTmplData p1

WHERE p1.SpecID IN

(

'Episode title'

,'Commercial brand'

,'Commercial product'

,'Commercial type'

,'Country of origin'

,'Year of production'

)

AND p.TemplateID IN (SELECT ItemID FROM dbo.InventTable) --Implicitly DISTINCT and as fast as a join.

GROUP BY p1.TemplateID, p1.SpecID

) --=== Now, pivot the data so that it's normalized instead of being an EAV-style result set and store it

-- all in a temporary lookup table for easy and very high performance joining in the final query.

SELECT TemplateID = ISNULL(TemplateID,0) --Makes the column in the lookup table NOT NULL.

, CREpisodeTitle = MAX(CASE WHEN SpecID = 'Episode title' THEN SpecIDs ELSE '' END)

, CRComBrand] = MAX(CASE WHEN SpecID = 'Commercial brand' THEN SpecIDs ELSE '' END)

, CRComProduct = MAX(CASE WHEN SpecID = 'Commercial product' THEN SpecIDs ELSE '' END)

, CRComType = MAX(CASE WHEN SpecID = 'Commercial type' THEN SpecIDs ELSE '' END)

, CRCountry = MAX(CASE WHEN SpecID = 'Country of origin' THEN SpecIDs ELSE '' END)

, CRYear = MAX(CASE WHEN SpecID = 'Year of production' THEN SpecIDs ELSE '' END)

INTO #Lookup_PstProdTmplData

FROM ctePreAgg

GROUP BY TemplateID

;

--===== Add a Primary Key for extra join performance. We let the system name the PK on Temp Tables

-- because such constraints must be uniquely named in the database and we don't want to destroy

-- the ability of more than one instance of the code to run conncurrently.

ALTER TABLE #Lookup_PstProdTmplData

ADD PRIMARY KEY CLUSTERED (TemplateID)

;

Once that's inplace, the final query becomes a blazing-performance cake walk. (Note that I didn't pre-aggregate/pivot all the tables that should be. You have to have some of the fun! 😛 )

SELECT i.ITEMID AS CRItemID, i.PSTPRODUCTID AS CRProductID, i.NAMEALIAS AS CRName, i.CREATIONPERFORMER AS CRPerformer,

i.CREATIONDURATION AS CRDuration, i.CREATIONSTATUS AS CRStatus, i.[MODIFIEDDATETIME] AS CRModifiedDateTime,

p.CREpisodeTitle, p.CRComBrand, p.CRComProduct, p.CRComType, p.CRCountry, p.CRYear

FROM dbo.InventTable i

JOIN #Lookup_PstProdTmplData p

ON p.TemplateID = i.ItemID

As a bit of a sidebar, this isn't Oracle and we don't have the 30 character object name limitation. Consider NOT using abbreviations for table names in the future as they serve only to daze and confuse the uninitiated. For example, whoever designed these tables used the name "PstProdTmplData" for a table that should have been named "PostProductionTemplate". It's only 7 characters longer and there's no chance of someone misreading the "Tmp" in the original abbreviated name as meaning "Temporary" especially if the miss the "l" in the name.

Also, we all know tables have data in them so the word "Data" in the table name is a bit superfluous.