T-SQL do a SELECT from SELECT

amaaiia — Tue, 12 Mar 2024 16:38:00 GMT

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 ','?

Re: T-SQL do a SELECT from SELECT

AndyDDC — Tue, 12 Mar 2024 19:44:47 GMT

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

;with distinctdata

(

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

)

select STRING_AGG(field1, ',') from distinctdata