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:

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

CodePudding user response：

First, let's clarify that stuff is, as Stu mentions, just used to remove the first delimiter. So, if you had

'a','b','c' 

and you wanted to use a commas delimiter, xml path would give you:

,a,b,c

and you use stuff to cut off the first comma:

a,b,c

Your query seems to have no delimiters at all, so you won't use stuff. With that out of the picture, here's me untested guess, it's tricky what with the dynamic query added on top of it:

SET @DynamicQuery = CONCAT (N'
    set @Sample =
            ( 
                select t.ColumnData
                from ', @CurrentTableName, ' AS x 
                where x.', @CurrentColumnName, ' IS NOT NULL
                for xml path(''''), type
            ).value(''.'',''nvarchar(max)'')
)t')

CodePudding user response：

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,'''')')