Use subquery after AS alias-CodePudding

I am trying to use subquery after alias AS in SQL server 2019, here is my sql query:

select concat(sum(labst),' kg') as (select distinct name1 from mb52 where werks='1202') from mb52 where werks='1202';

but this query giving this error:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '('.

Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'from'.

I want to get the result like this:

1202
--------------------------------------------
343979.535 kg

instead of using select concat(sum(labst),' kg') as "1202", because I get the value of werks from a variable $werks and my final SQL query look like this:

select concat(sum(labst),' kg') as (select distinct name1 from mb52 where werks in ($werks)) from mb52 where werks in ($werks);

Can anyone help me on this problem,please?

CodePudding user response：

You can use dynamic sql like this

declare @sql varchar(1000)
declare @name varchar(100)
declare @werks varchar(1000) = '1202, 1200'

select distinct
       @name = name1 
from   mb52
where  werks = 1202


set @sql = 'select concat(sum(labst), '' kg'') as '''   @name   ''' from mb52 where werks in ('   @werks   ')'

exec (@sql)

See this DBFiddle where you can try it yourself

However, there is a major problem with your query, as you write

as (select distinct name1 from mb52 where werks in ($werks))

This can return more than one value for name1 if there are more then one value in $werks

So I changed it to = 1202 like the other answer also did
Your query probably needs to more like this

set @sql2 = 'select concat(sum(labst), '' kg'') as '''   @name   ''' from mb52 where werks = 1202'

where again I changed the in $werks into = 1202 because I think it will produce wrong results, but that is up to you.
Look at the dbfiddle and play with it until it does what you need.

EDIT

As requested in the comments, here is how the query looks like with just one value in $werks

set @sql2 = 'select concat(sum(labst), '' kg'') as '''   @name   ''' from mb52 where werks = '   @werks

The DBFiddle is also altered

CodePudding user response：

you should use dynamic sql to get this working

DECLARE @S NVARCHAR(MAX)
SELECT distinct @S = 'select concat(sum(labst),'' kg'') as '  name1  ' from mb52 where werks=''1202''' from mb52 where werks='1202'
EXEC (@S)

CodePudding user response：

To do this, you need to use a dynamic query like this:

DECLARE @query NVARCHAR(MAX) = 'select concat(sum(t1.labst),'' kg'') as '   (SELECT DISTINCT
t1.name1  FROM dbo.mb52 t1  WHERE t1.werks = '1202')   ' from dbo.mb52 t1 where    t1.werks=''1202'''
EXEC sp_executesql @query