How to use variable after as in statement of SQL Server-CodePudding

Origin table

So So_Line So other
ABC 2 rrr35
BDC 2 rrr35

Result table

So So_Line So other
ABC-1 2 rrr35
ABC-2 2 rrr35
ABC-3 2 rrr35
ABC-4 2 rrr35
ABC-5 2 rrr35
BDC-1 2 rrr35
BDC-2 2 rrr35

It didn't work this way

DECLARE @MyVariable VARCHAR(max);
SET @MyVariable = 'So,So_Line,So_other';

SELECT CONCAT(So, t.x) AS @MyVariable
FROM [test].[dbo].[foo] 
CROSS JOIN (VALUES('-1'),('-2'),('-3'),('-4'),('-5')) t(x) 
WHERE So = 'ABC'

UNION

SELECT CONCAT(So, t.x) AS @MyVariable
FROM [test].[dbo].[foo] 
CROSS JOIN (VALUES('-1'),('-2')) t(x) 
WHERE So = 'BDC'

But it did work this way:

SELECT CONCAT(So, t.x) AS So, So_Line, So_other
FROM [test].[dbo].[foo] 
CROSS JOIN (VALUES('-1'),('-2'),('-3'),('-4'),('-5')) t(x) 
WHERE So = 'ABC'

UNION 

SELECT CONCAT(So, t.x) AS So, So_Line, So_other 
FROM [test].[dbo].[foo] 
CROSS JOIN (VALUES('-1'),('-2')) t(x) 
WHERE So = 'BDC'

The table is just example, the table what I deal with have like 300 columns and I can't just add after the as, so I think I can create a variable.

But it didn't work near @MyVariable, could anyone help me?

CodePudding user response：

Variable is a value holder so it SQL Server treats it accordingly.

To achieve the desired result you should go for dynamic query.

DECLARE @MyVariable VARCHAR(max);
SET @MyVariable = 'So,So_Line,So_other';
DECLARE @Dynamic_Query NVARCHAR(MAX)

SET @Dynamic_Query=' SELECT CONCAT(So, t.x) as [' @MyVariable ']
FROM   [test].[dbo].[foo] CROSS JOIN (VALUES(''-1''),(''-2''),(''-3''),(''-4''),(''-5'')) t(x) WHERE  So = ''ABC''
Union
SELECT CONCAT(So, t.x)
FROM   [test].[dbo].[foo] CROSS JOIN (VALUES(''-1''),(''-2'')) t(x) WHERE  So = ''BDC'''

exec(@Dynamic_Query)

Thanks!