Port STRING_AGG to FOR XML

Question

I'm currently working on Data Discovery and Classification and I have a query that allows me to see a preview of the data while doing the classification.

This is an example of how it works against AdventureWorks:

DECLARE @TableName VARCHAR(100) = 'Product'

DROP TABLE IF EXISTS #ColumnsToDisplay

SELECT    ROW_NUMBER () OVER (ORDER BY tab.name) AS Iteration,
          SCHEMA_NAME (tab.schema_id) AS schema_name,
          tab.name AS table_name,
          --col.column_id,
          col.name AS column_name,
          --t.name AS data_type,
          --col.max_length,
          --col.precision,
          CAST(NULL AS VARCHAR(MAX)) AS DataSample
INTO      #ColumnsToDisplay
FROM      sys.tables AS tab
JOIN      sys.columns AS col
    ON    col.object_id = tab.object_id
--LEFT JOIN sys.types AS t
--    ON    col.user_type_id = t.user_type_id
WHERE     tab.name = @TableName

DECLARE @Iterations       INT = 0,
        @CurrentIteration INT = 1;

SELECT @Iterations = MAX (Iteration)
FROM   #ColumnsToDisplay 
WHILE @CurrentIteration <= @Iterations
BEGIN
    DECLARE @CurrentTableName  VARCHAR(100)   = '',
            @CurrentColumnName VARCHAR(100)   = '',
            @DynamicQuery      NVARCHAR(1000) = N''
    DECLARE @Sample VARCHAR(MAX)

    SET @CurrentTableName = '';
    SET @DynamicQuery = N'';

    SELECT @CurrentTableName = CONCAT (ttq.schema_name, '.', ttq.table_name),
           @CurrentColumnName = ttq.column_name
    FROM   #ColumnsToDisplay AS ttq
    WHERE  ttq.Iteration = @CurrentIteration

    IF (@CurrentTableName = '')
    BEGIN
        SET @CurrentIteration += 1

        CONTINUE
    END

    SET @DynamicQuery = CONCAT (N'
    SELECT @Sample = STRING_AGG(t.ColumnData,'', '') 
    FROM (
            SELECT TOP 5  CAST(x.', @CurrentColumnName, ' AS VARCHAR(MAX)) AS ColumnData 
            FROM ', @CurrentTableName, ' AS x 
            WHERE x.', @CurrentColumnName, ' IS NOT NULL
    )t')

    EXECUTE sys.sp_executesql @DynamicQuery,
                              N'@Sample VARCHAR(MAX) OUTPUT',
                              @Sample = @Sample OUTPUT

    UPDATE #ColumnsToDisplay
    SET    DataSample = @Sample
    WHERE  Iteration = @CurrentIteration

    SET @CurrentIteration += 1
END

SELECT ctd.Iteration,
       ctd.schema_name,
       ctd.table_name,
       --ctd.column_id,
       ctd.column_name,
       --ctd.data_type,
       --ctd.max_length,
       --ctd.precision,
       ctd.DataSample
FROM   #ColumnsToDisplay AS ctd

Here the result:

Iteration	schema_name	table_name	column_name	DataSample
1	Production	Product	ProductID	980, 365, 771, 404, 977
2	Production	Product	Name	Adjustable Race, All-Purpose Bike Stand, AWC Logo Cap, BB Ball Bearing, Bearing Ball
3	Production	Product	ProductNumber	AR-5381, BA-8327, BB-7421, BB-8107, BB-9108
4	Production	Product	MakeFlag	0, 0, 1, 0, 1
5	Production	Product	FinishedGoodsFlag	0, 0, 0, 0, 0
6	Production	Product	Color	Black, Black, Black, Silver, Silver

The problem is that this query only works from SQL Server 2017 and above because it uses STRING_AGG. For SQL Server 2016 and below I'm supposed to use STUFF instead.

I followed this example but I couldn't really fix it.

The only thing I know is that the part of the code that I need to port is this:

SET @DynamicQuery = CONCAT (N'
SELECT @Sample = STRING_AGG(t.ColumnData,'', '') 
FROM (
        SELECT TOP 5  CAST(x.', @CurrentColumnName, ' AS VARCHAR(MAX)) AS ColumnData 
        FROM ', @CurrentTableName, ' AS x 
        WHERE x.', @CurrentColumnName, ' IS NOT NULL
)t')

Can anyone help me port STRING_AGG to STUFF?

Thank you

Answer 1

Than you for your help.

The right question was:

SET @DynamicQuery = CONCAT (N'
SELECT @Sample =  STUFF((SELECT '', ''+ t.ColumnData  
FROM (
        SELECT TOP 5 CAST(x.[', @CurrentColumnName, '] AS VARCHAR(MAX)) AS ColumnData 
        FROM ', @CurrentTableName, ' AS x 
        WHERE x.[', @CurrentColumnName, '] IS NOT NULL 
) AS t
   FOR XML PATH('''')),1,1,'''')')