capture the string_agg vlues in the results as a column name

Question

I have a stored proc that dynamically retrieves the key fields for each table identified from the system tables. Due to the variation in the number of Key fields in the different tables the STRING_AGG function is successfully used to extract the fields and then use them in the SQL with the Concat function. What I can't seem to be able to do is to include the concatenated column names in my output in the results table as I need to see the tablename, the actual columnnames that have been concatenated, count of duplicates and the values. The @table_name enclosed in 3 single quotes works fine but the keyfields does not regardless of the number of single quotes, even though the Print statement shows values of keyfield1'|'keyfield2.

CREATE PROCEDURE DynamicKeyFieldAggregation 
    @TableName NVARCHAR(128)
AS
BEGIN
    DECLARE @KeyFields NVARCHAR(MAX);
    DECLARE @Sql NVARCHAR(MAX);
    -- Fetch key fields
    SELECT @KeyFields = STRING_AGG(NAME, ',''|'',') 
    FROM sys.columns 
    WHERE object_id = OBJECT_ID(@TableName) 
      AND name LIKE '%_Key';
---view the fields extracted    
@Print @KeyFields
    -- Construct the dynamic query return top 5 random results using NEWID()
    SET @Sql = 'Insert Into dbo.resultstable
                (TableName, ColumnName,TestResult, KeyFieldValues, created_on)
                SELECT Top(5) '''+@table_name+''' as tablename,''' + KeyFields +''' as ColumnName,
                COUNT(*) as TestResult, CONCAT(' + @KeyFields + ') as KeyFieldValues,
                GETDATE() as created_on
                FROM ' + @TableName + ' 
                GROUP BY CONCAT(' + @KeyFields + ')
                HAVING COUNT(*) > 1
                ORDER BY NEWID()';
    -- Execute the dynamic query
    EXEC sp_executesql @Sql;
END

Answer 1

Dear @Binway ,

To analyze the query print the @Sql parameter, just before executing the query.

I got the following result:

Insert Into dbo.resultstable
(TableName, ColumnName,TestResult, KeyFieldValues, created_on)
SELECT Top(5) 'TestTable' as tablename,'ListDate,'|',blaat' as ColumnName,
COUNT(*) as TestResult, CONCAT(Column1,'|',Column2) as KeyFieldValues,
GETDATE() as created_on
FROM TestTable
GROUP BY CONCAT(ListDate,'|',blaat)
HAVING COUNT(*) > 1
ORDER BY NEWID()

The problem is in the quotes on the ColumnName column (highlighted with the arrow):

You have to replace them by two quotes, so it will be inserted as one string:

Insert Into dbo.resultstable
(TableName, ColumnName,TestResult, KeyFieldValues, created_on)
SELECT Top(5) 'TestTable' as tablename,'Column1,''|'',Column2' as ColumnName,
COUNT(*) as TestResult, CONCAT(ListDate,'|',blaat) as KeyFieldValues,
GETDATE() as created_on
FROM TestTable 
GROUP BY CONCAT(ListDate,'|',blaat)
HAVING COUNT(*) > 1
ORDER BY NEWID()

Kind Regards,

Wilko

---

Please do not forget to "Accept the answer” wherever the information provided helps you, this can be beneficial to other community members. If you have extra questions about this answer, please click "Comment".

Answer 2

Thanks to Wilko telling me about printing the SQL I was able to deduce how to get this done.

An additional line in the code to extracts the fields names then inject them into the SQL with QuoteName

CREATE PROCEDURE DynamicKeyFieldAggregation      @TableName NVARCHAR(128) AS BEGIN 
  DECLARE @KeyFields NVARCHAR(MAX);  
   DECLARE @Sql NVARCHAR(MAX);   
  -- Fetch key fields   
  SELECT @KeyFields = STRING_AGG(NAME, ',''|'','),
		@ColumnNames =   STRING_AGG(NAME, '_')
   FROM sys.columns  
    WHERE object_id = OBJECT_ID(@TableName) 
    AND name LIKE '%_Key';
 ---view the fields extracted 
    @Print @KeyFields  
  -- Construct the dynamic query return top 5 random results using NEWID()  
  SET @Sql = 'Insert Into dbo.resultstable       
  (TableName, ColumnName,TestResult, KeyFieldValues, created_on)  
  SELECT Top(5) '''+@table_name+''' as tablename,QUOTENAME(''' + @ColumnNames +''') as ColumnName,                 COUNT(*) as TestResult, CONCAT(' + @KeyFields + ') as KeyFieldValues,                 GETDATE() as created_on                 FROM ' + @TableName + ' 
             GROUP BY CONCAT(' + @KeyFields + ')    
             HAVING COUNT(*) > 1  
            ORDER BY NEWID()'; 
    -- Execute the dynamic query  
   EXEC sp_executesql @Sql; END