Reuse a subquery result in another column in main query-CodePudding

In the below query, the columns GLName and GLDesc fetch the same value, but I need to have the same value with 2 different column names to satisfy the requirement of a predefined Excel template.

SELECT T0.GLCode, (SELECT ST0.Name From GL ST0 WHERE ST0.Code = T0.GLCode) AS GLName, 
(SELECT ST0.Name FROM GL ST0 WHERE ST0.Code = T0.GLCode) AS GLDesc
FROM Trans T0

How can I reuse the result of the 1st subquery to avoid writing the 2nd subquery? This is a simplified example. In the real query, around 10 subqueries need to be reused in another subquery. In my case, it is difficult to build another table with all the required values from which to fetch the values in main query, as the master tables are huge and the values to be fetched from the master tables are only for specific codes which are based on the filtered records from the Trans table.

EDIT 1:

I am specifically asking about reuse of subquery. I do not want to use JOINs here because the real query with several JOINs takes around 60 seconds, whereas the query with subquery takes about 7-8 seconds. The only inconvenience is that I have to write the same subquery multiple times.

CodePudding user response：

Putting your subquery in an apply makes it available for reuse:

select t.GLCode, g.GLName, g.GLName GLDesc
from Trans t
outer apply (
  select [Name] GLName
  from GL
  where GL.Code = t.GLCode
)g;

CodePudding user response：

Just use proper join then:

SELECT T0.GLCode, ST0.Name AS GLName, ST0.Name AS GLDesc
FROM Trans T0
    INNER JOIN GL ST0 ON T0.GLCode = ST0.Code