SQL Format string

Im working on an importscript in SQL which is working fine at this point except one part which is the STRING_AGG part. I need this to be in a specific format but I can’t seem to find a way to get this done.

Currently it’s outputting: 00000000-0000-0000-0000-000000000000, 00000000-0000-0000-0000-000000000000.

What I need it to be is: ["00000000-0000-0000-0000-000000000000","00000000-0000-0000-0000-000000000000"]

I’ve tried some things with CONCAT but didn’t get any output at all, how can this be done?

        WITH CTE_EnrichmentProperty (EntityId, EnrichmentIndex, IbProductKey) AS (
         SELECT DISTINCT EntityId
                        , EnrichmentIndex
                        , IbProductKey
                 FROM dbo.ArticleEnrichmentImportItem
                 WHERE ArticleEnrichmentImportItem.ArticleEnrichmentImportId = @ImportId
                   AND ArticleEnrichmentImportItem.EnrichmentType = 'EnrichmentPropertySelection'
                   AND ArticleEnrichmentImportItem.State IN (1, 2, 3)
        )
            INSERT INTO Attribute (Id, Type, EnrichmentProperty_EnrichmentPropertyId, EnrichmentProperty_Value)
        SELECT ArticleEnrichmentImportItem.Id,
               'enrichment',
               CTE_EnrichmentProperty.EntityId,
               (SELECT STRING_AGG(CAST(EntityId AS varchar(38)), ',') FROM dbo.ArticleEnrichmentImportItem A 
                    WHERE A.ArticleEnrichmentImportId = @ImportId
                    AND A.EnrichmentType = 'EnrichmentPropertySelectionValue'
                    AND A.EnrichmentIndex = ArticleEnrichmentImportItem.EnrichmentIndex
                    And A.IbProductKey = ArticleEnrichmentImportItem.IbProductKey
                )
        FROM dbo.ArticleEnrichmentImportItem
              JOIN CTE_EnrichmentProperty ON CTE_EnrichmentProperty.EnrichmentIndex = ArticleEnrichmentImportItem.EnrichmentIndex 
            WHERE ArticleEnrichmentImportItem.ArticleEnrichmentImportId = @ImportId
              AND ArticleEnrichmentImportItem.EnrichmentType = 'EnrichmentPropertySelectionValue'
                      AND ArticleEnrichmentImportItem.IbProductKey = CTE_EnrichmentProperty.IbProductKey;

>Solution :

...
SELECT CONCAT( '["', 
               STRING_AGG(CAST(EntityId AS varchar(38)), '","'),
               '"]'
               ),
...

Leave a Reply