Sql-server – Possible to CROSS JOIN a subquery without duplicating code

sql serversql-server-2012

I'm wanting to get all permutations between two columns in the same table.

Currently my query is this:

declare @tgt int = 123456;
select *
from (
  select T.id, T.parentId
  from Task T
  where (
    (T.id = @tgt and T.typeId = 3)
    or
    (T.parentId = @tgt and T.typeId = 1)
  )
) WF1
cross join (
  select T.id, T.parentId
  from Task T
  where (
    (T.id = @tgt and T.typeId = 3)
    or
    (T.parentId = @tgt and T.typeId = 1)
  )
) WF2;

Note that subquery WF1 is the same as WF2. In order to be DRY, I'd really like to do this:

declare @tgt int = 123456;
select *
from (
  select T.id, T.parentId
  from Task T
  where (
    (T.id = @tgt and T.typeId = 3)
    or
    (T.parentId = @tgt and T.typeId = 1)
  )
) WF
cross join WF;

But this is not working in SQL Server 2012.

Is there some technique I can apply to avoid duplicating the query?

Best Answer

You can use a CTE:

WITH WF AS (
    select T.id, T.parentId
    from Task T
    where 
        (T.id = @tgt and T.typeId = 3)
        or
        (T.parentId = @tgt and T.typeId = 1)
)
SELECT * 
FROM WF AS WF1
CROSS JOIN WF AS WF2
Related Question