SELECT  ta.ALIAS AS attribute
      , ta.LABEL
FROM ITEM
  INNER JOIN CONTAINER_ITEM_LINK conItemLink ON item.ID = conItemLink.ITEM_ID
    AND conItemLink.ID = (SELECT MAX(link.ID) FROM CONTAINER_ITEM_LINK link WHERE link.ITEM_ID = conItemLink.ITEM_ID AND link.CONTAINER_TYPE_ID = 3 AND link.IN_FLAG = 'Y')
  INNER JOIN ITEM_STATUS_HISTORY ish ON conItemLink.ITEM_ID = ish.ITEM_ID
    AND ish.id = (SELECT MAX(ID) FROM ITEM_STATUS_HISTORY WHERE ITEM_ID = conItemLink.ITEM_ID) AND ish.ITEM_STATUS_ID NOT IN (3, 4)
  INNER JOIN TEMPLATE2_INSTANCE ti ON ITEM.TEMPLATE_INSTANCE_ID = ti.ID
  INNER JOIN TEMPLATE2 temp ON ti.TEMPLATE_ID = temp.ID
  INNER JOIN TEMPLATE_ATTRIBUTE ta ON temp.ID = ta.TEMPLATE_ID
  INNER JOIN TEMPLATE_ATTRIBUTE_PROP propLink ON ta.ID = propLink.ATTRIBUTE_ID AND propLink.PROPERTY_ID IN (33)
WHERE item.HID.GetLevel() = 1
  AND conItemLink.CONTAINER_ID = 182258
GROUP BY IIF(propLink.PROPERTY_ID=22, propName.ALIAS, ta.ALIAS), ta.LABEL

SELECT [other], [BagID], [ItemID]
      , SUM( CAST(ISNULL(quantity, 1) AS INT)) AS quantity
FROM
    (
        SELECT  base.*
          FROM
            (
                SELECT filter.attribute
                       , filter.value
                       , filter.INSTANCE_ID
                FROM
                    (
                      SELECT IIF(propLink.PROPERTY_ID=22, propName.ALIAS, ta.NAME) AS attribute
                            , tiav.value
                            , IIF((COUNT(*) OVER (PARTITION BY tiav.INSTANCE_ID) > 1), 2, 1) AS count
                            , propLink.PROPERTY_ID
                            , tiav.INSTANCE_ID
                      FROM ITEM
                        INNER JOIN CONTAINER_ITEM_LINK conItemLink ON item.ID = conItemLink.ITEM_ID
                          AND conItemLink.ID = (SELECT MAX(link.ID) FROM CONTAINER_ITEM_LINK link WHERE link.ITEM_ID = conItemLink.ITEM_ID AND link.CONTAINER_TYPE_ID = 3 AND link.IN_FLAG = 'Y')
                        INNER JOIN ITEM_STATUS_HISTORY ish ON conItemLink.ITEM_ID = ish.ITEM_ID
                          AND ish.id = (SELECT MAX(ID) FROM ITEM_STATUS_HISTORY WHERE ITEM_ID = conItemLink.ITEM_ID) AND ish.ITEM_STATUS_ID NOT IN (3, 4)
                        INNER JOIN TEMPLATE2_INSTANCE ti ON ITEM.TEMPLATE_INSTANCE_ID = ti.ID
                        INNER JOIN TEMPLATE2 temp ON ti.TEMPLATE_ID = temp.ID
                        INNER JOIN TEMPLATE_ATTRIBUTE ta ON temp.ID = ta.TEMPLATE_ID
                        INNER JOIN TEMPLATE_ATTRIBUTE_PROP propLink ON ta.ID = propLink.ATTRIBUTE_ID AND propLink.PROPERTY_ID IN (33, 22)
                        INNER JOIN TEMPLATE_ATTRIBUTE_PROP_DEF propName ON propLink.PROPERTY_ID = propName.ID
                        INNER JOIN TEMPLATE_INSTANCE_ATTR_VAL tiav ON tiav.ATTRIBUTE_ID = ta.ID AND tiav.INSTANCE_ID = item.TEMPLATE_INSTANCE_ID
                      WHERE item.HID.GetLevel() = 1
                        AND conItemLink.CONTAINER_ID = 182258
                    ) filter
                WHERE NOT (filter.PROPERTY_ID = 22 AND filter.count = 1)
                UNION ALL
                SELECT DISTINCT IIF(propLink.PROPERTY_ID=22, propName.ALIAS, 'other') AS attribute
                      , IIF(propLink.PROPERTY_ID=22, ISNULL(tiav.value, '1'), 'other') AS value
                      , item.TEMPLATE_INSTANCE_ID AS INSTANCE_ID
                FROM ITEM
                    INNER JOIN CONTAINER_ITEM_LINK conItemLink ON item.ID = conItemLink.ITEM_ID
                      AND conItemLink.ID = (SELECT MAX(link.ID) FROM CONTAINER_ITEM_LINK link WHERE link.ITEM_ID = conItemLink.ITEM_ID AND link.CONTAINER_TYPE_ID = 3 AND link.IN_FLAG = 'Y')
                    INNER JOIN ITEM_STATUS_HISTORY ish ON conItemLink.ITEM_ID = ish.ITEM_ID
                      AND ish.id = (SELECT MAX(ID) FROM ITEM_STATUS_HISTORY WHERE ITEM_ID = conItemLink.ITEM_ID) AND ish.ITEM_STATUS_ID NOT IN (3, 4)
                    INNER JOIN TEMPLATE2_INSTANCE ti ON ITEM.TEMPLATE_INSTANCE_ID = ti.ID
                    INNER JOIN TEMPLATE2 temp ON ti.TEMPLATE_ID = temp.ID
                    LEFT JOIN TEMPLATE_ATTRIBUTE ta ON temp.ID = ta.TEMPLATE_ID
                    LEFT JOIN TEMPLATE_ATTRIBUTE_PROP propLink ON ta.ID = propLink.ATTRIBUTE_ID AND propLink.PROPERTY_ID = 22
                    LEFT JOIN TEMPLATE_ATTRIBUTE_PROP_DEF propName ON propLink.PROPERTY_ID = propName.ID
                    LEFT JOIN TEMPLATE_INSTANCE_ATTR_VAL tiav ON tiav.ATTRIBUTE_ID = ta.ID AND tiav.INSTANCE_ID = item.TEMPLATE_INSTANCE_ID AND propLink.PROPERTY_ID = 22

                WHERE item.HID.GetLevel() = 1
                  AND conItemLink.CONTAINER_ID = 182258
                  AND (
                    SELECT SUM(IIF(propLink.PROPERTY_ID = 33, 1, 0))
                      FROM TEMPLATE_ATTRIBUTE ta
                        INNER JOIN TEMPLATE_ATTRIBUTE_PROP propLink ON ta.ID = propLink.ATTRIBUTE_ID
                        INNER JOIN TEMPLATE_INSTANCE_ATTR_VAL tiav ON tiav.ATTRIBUTE_ID = ta.ID AND tiav.INSTANCE_ID = ti.ID AND tiav.value IS NOT NULL
                    WHERE ti.ID = item.TEMPLATE_INSTANCE_ID
                      AND ta.TEMPLATE_ID = temp.ID
                  ) = 0

            ) base
    ) pivotBase
PIVOT (
        MAX(pivotBase.value)
        FOR pivotBase.attribute IN ([other], [BagID], [ItemID], [quantity])
      ) main
GROUP BY [other], [BagID], [ItemID]