SELECT
vw_Items.ID
,vw_Items.ResolvedEntityID
,vw_Items.ResolvedDescription
,vw_Items.ItemProductNumber
,vw_Items.ItemDescription
,vw_Items.FilteringType
,vw_Items.ItemCategory
,vw_Items.IsDefault
,vw_Items.IsInventoryType
,vw_Items.Active
,vw_Items.PlantFloorVisible
,[dbo].f_ParseDistanceValue([Length]) AS 'Length'
,[dbo].f_ParseDistanceValue(Width) AS Width
,ISNULL(MaterialDescription.[Description], '(none)') AS 'Material'
,ISNULL(GradeDescription.[Description], '(none)') AS 'Grade'
,ISNULL(MillingDescription.[Description], '(none)') AS 'Milling'
,ISNULL(StateDescription.[Description], '(none)') AS 'State'
FROM
vw_Items
-- Join to get properties
left join (
    select [ItemEntityID],[Property],[Value] from [dbo].[ItemProperties]) p
    PIVOT (MAX(VALUE) for [Property] in (Material,Grade,Milling,State,Length,Width))properties on vw_Items.[EntityID] = properties.[ItemEntityID]
LEFT JOIN DescriptionEntity  AS MaterialDescription ON properties.Material = MaterialDescription.EntityID
LEFT JOIN DescriptionEntity  AS GradeDescription ON properties.Grade = GradeDescription.EntityID
LEFT JOIN DescriptionEntity  AS MillingDescription ON properties.Milling = MillingDescription.EntityID
LEFT JOIN #DescriptionEntity  AS StateDescription ON properties.[State] = StateDescription.EntityID