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)), '","'),
'"]'
),
...