can I turn a select from a select statement into a self join?

Question

This is probably a stupid question but i cannot figure this out I have query like this :

select TERM_SID
,max(PUBLICDATA) as PublicData
,max(PUBLIC_AGGREGATE) as Public_AGGregate
,max(INTERNAL) as Internal
,max(INTERNAL_AGGREGATE) as internal_Aggregate
,max(LIMITED) as Limited
,max(RESTRICTED) as Restricted
from
(
      SELECT  TERM_SID,
      CASE WHEN d2.DATA_CLASSIFICATION_DESC='Public data' THEN 'Y' ELSE 'N' END AS 'PUBLICDATA',
      CASE WHEN d2.DATA_CLASSIFICATION_DESC='Public data in aggregate' THEN 'Y'  ELSE 'N' END as 'PUBLIC_AGGREGATE',
      CASE WHEN d2.DATA_CLASSIFICATION_DESC='Internal data' THEN 'Y'  ELSE 'N' END as 'INTERNAL',
      CASE WHEN d2.DATA_CLASSIFICATION_DESC='Internal data in aggregate' THEN 'Y'  ELSE 'N' END as 'INTERNAL_AGGREGATE',
      CASE WHEN d2.DATA_CLASSIFICATION_DESC='Limited data' THEN 'Y'  ELSE 'N' END as 'LIMITED',
      CASE WHEN d2.DATA_CLASSIFICATION_DESC='Restricted data' THEN 'Y'  ELSE 'N' END as 'RESTRICTED'
      FROM [UDW_DATA_DICTIONARY].[dbo].[DATA_CLASSIFICATION] d2
      JOIN [UDW_DATA_DICTIONARY].[dbo].[DATA_CLASSIFICATION_MAPPING] dm2 ON dm2.DATA_CLASSIFICATION_SID=d2.DATA_CLASSIFICATION_SID
) mat
group by Term_SID

and I have tried to convert it but i keep getting tripped up by the join in the inner select , is there a way to rebuild this with a join to improve performance.

Answer 1

Turn it into a derived table using a Common Table Expression (CTE) and you can reference it as if it were any other table:

with mat as
(
      SELECT  TERM_SID
             ,CASE WHEN d2.DATA_CLASSIFICATION_DESC = 'Public data' THEN 'Y' ELSE 'N' END AS PUBLICDATA
             ,CASE WHEN d2.DATA_CLASSIFICATION_DESC = 'Public data in aggregate' THEN 'Y'  ELSE 'N' END as PUBLIC_AGGREGATE
             ,CASE WHEN d2.DATA_CLASSIFICATION_DESC = 'Internal data' THEN 'Y'  ELSE 'N' END as INTERNAL
             ,CASE WHEN d2.DATA_CLASSIFICATION_DESC = 'Internal data in aggregate' THEN 'Y'  ELSE 'N' END as INTERNAL_AGGREGATE
             ,CASE WHEN d2.DATA_CLASSIFICATION_DESC = 'Limited data' THEN 'Y'  ELSE 'N' END as LIMITED
             ,CASE WHEN d2.DATA_CLASSIFICATION_DESC = 'Restricted data' THEN 'Y'  ELSE 'N' END as RESTRICTED
      FROM [UDW_DATA_DICTIONARY].[dbo].[DATA_CLASSIFICATION] d2
          JOIN [UDW_DATA_DICTIONARY].[dbo].[DATA_CLASSIFICATION_MAPPING] dm2
              ON dm2.DATA_CLASSIFICATION_SID = d2.DATA_CLASSIFICATION_SID
)
select m1.TERM_SID
      ,m1.max(PUBLICDATA) as PublicData
      ,m1.max(PUBLIC_AGGREGATE) as Public_AGGregate
      ,m1.max(INTERNAL) as Internal
      ,m1.max(INTERNAL_AGGREGATE) as internal_Aggregate
      ,m1.max(LIMITED) as Limited
      ,m1.max(RESTRICTED) as Restricted

      ,m2.count(1) as mat2count
from mat m1
    left join mat m2
        on(m1.TERM_SID = m2.TERM_SID)
group by Term_SID