Forum Discussion

amaaiia's avatar
amaaiia
Skilled Sharer
2 years ago
Solved

T-SQL do a SELECT from SELECT

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

 

data warehouse
  • AndyDDC's avatar
    AndyDDC
    2 years ago

    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

1 Reply

  • AndyDDC's avatar
    AndyDDC
    Most Valuable Professional
    2 years ago

    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