I have a dbo.tbl1 with columns A, B, C, etc... And a query that looks like this:

select 'random description' as [description], A as [volume] from dbo.tbl1

UNION ALL

select 'another random description' as [description], B as [volume] from dbo.tbl1

UNION ALL

select 'yet another random description' as [description], C as [volume] from dbo.tbl1

This goes on for many more queries...comining into a giant CTE with essentially 2 columns.

This is incredibly inefficient, because SQL server is doing a table scan for each of those (many) queries, and tbl1 is many millions of rows, even though all queries are coming from the same table.

How do I write this, telling SQL server to only use 1 table scan? I'd rather not change tbl1...

I've tried reading up on this, and applying index isn't useful here because I'm literally just asking the whole table every time. So I'm looking for something else.

CodePudding user response：

Assuming following setup with two rows in the table...

CREATE TABLE tbl1
(
A int,
B int,
C int 
)

insert tbl1 VALUES (1,2,3),
                   (4,5,6);

... and that you would want these results

Then you can use

SELECT Description,
       Volume
FROM   tbl1
       CROSS apply ( VALUES (A, 'random description'),
                            (B, 'another random description'),
                            (C, 'yet another random description') 
                    )d(Volume, Description); 

To do the unpivoting of pairs of values