T-SQL do a SELECT from SELECT

[amaaiia]

I have an A table in a warehouse. I want to query the table as follows:

select STRING_AGG(field1, ',') from (select distinct field1 from [lakehouse].[schema].[table] where field2='value2')

And I get this error message: 

Is it possible to query a subquery? How can I get distinct values and then get them as a string separated by ','?

[AndyDDC · Accepted solution]

Hi @amaaiia @can you try putting your select in a CTE:

;with distinctdata 

as

(

select distinct field1 from [lakehouse].[schema].[table] where field2='value2'

)

select STRING_AGG(field1, ',') from distinctdata