SQL Format string

Question

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;

The mysql equivalent of string_agg is group_concat dev.mysql.com/doc/refman/8.0/en/… — P.Salmon
– P.Salmon, Commented Nov 8, 2022 at 9:28
I tried that but its not recognized as a function Are you sure that you use MySQL and not MS SQL (SQL Server)? — Akina
– Akina, Commented Nov 8, 2022 at 9:32

Akina · Accepted Answer · 2022-11-08 09:36:12Z

1

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

answered Nov 8, 2022 at 9:36

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Heis Over a year ago

Thanks, I tried concat but didn't get it to work myself, guess I should spend more time understanding the documentation again

Collectives™ on Stack Overflow

SQL Format string

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related