Multiple SELECTs from one nested SELECT

Question

I'm running multiple SELECTs in one query, and they are using the same nested FROM SELECT. The code goes something like this:

SELECT B FROM (SELECT A) DATA; 
SELECT C FROM (SELECT A) DATA;
SELECT D FROM (SELECT A) DATA;
SELECT E FROM (SELECT A) DATA

Is there a way to "store" SELECT A (variable? other way?) and use it across this one query or do I need to repeat it every single time? SELECT A is quite large and I'd like to make this query as clean as possible.

This is it! I know it was a *facepalm* question, but that was as far as my knowledge got me. Thank you very much! — bamboun, Nov 11 '21 at 09:32

score 3 · Answer 1 · answered Nov 11 '21 at 09:41

Oh, there's a variety of options, depending on your use case:

If the nested SELECT never changes, you can create a view.
If all the statements are executed in the same session and the filtering is mostly done in the nested SQL, you can select its result into a temp table or a temp variable.
If those SQL statements are created in code, you can create a common table expression (CTE). Yes, you still need to include the CTE in every single SQL, but you only have to append it at the beginning of each SELECT statement rather than in the middle, which might make your code more readable.

Jelle · Answer 2 · 2021-11-11T09:48:39.423

You can create VIEW so that you only have to write the A query once, but if you only want to execute the data within the query once, you can also save the data in a variable. You can read about the difference here. Example below.

DECLARE @A TABLE (
    B NVARCHAR(250) NOT NULL
,   C INT NOT NULL
,   D NVARCHAR(250) NULL
)

INSERT INTO @A (B, C, D)
SELECT 
    [ColumnB]
,   [ColumnC]
,   [ColumnD]
FROM [dbo].[MyTable] 

SELECT [B] FROM @A; 
SELECT [C] FROM @A; 
SELECT [D] FROM @A;

score 0 · Answer 3 · answered Nov 11 '21 at 10:46

first solution Execute the query as text

declare @QuerySub_Str  Nvarchar(500);
set @QuerySub_Str ='SELECT A'
declare @QueryFinal_Str Nvarchar(4000);
set @QueryFinal_Str = 'SELECT B FROM (' + @QuerySub_Str +') DATA; 
SELECT C FROM (' + @QuerySub_Str +') DATA;
SELECT D FROM (' + @QuerySub_Str +') DATA;
SELECT E FROM (' + @QuerySub_Str +' DATA;'
EXEC sp_executesql @sql

the best solution Create a view containing the partial query Use it in the final query

Create VIEW [dbo].[View_1]
AS
SELECT  1 as A , 2 As B  , 3 As C , 4 As D  , 5 As E  
FROM            Table_1
GO

SELECT B FROM View_1 ; 
SELECT C FROM View_1 ;
SELECT D FROM View_1 ;
SELECT E FROM View_1 ;

Multiple SELECTs from one nested SELECT

3 Answers3