Sql string replace issue

Question

I have a parameter in stored procedure named grouplist. This contain column names in comma seperated values.

for eg: @grouplist='Class1,Class2,Class3'

My query is

Set @Sql= 'select ' + @grouplist +' from #final

My result is

These columns contains value "ZZTotal" value. Its should be replaced with "Total" .These are dynamic columns.

I need to replace zztotal with total.

In the final output it should be like this

Any help appreciated

Answer 1

There is a larger problem that is being ignored: there is most likely a reason that the text is already ZZTotal instead of Total. And that reason is most likely in order to maintain proper row ordering (i.e. keeping the totals at the bottom).

The ZZTotal rows are most likely being generated automatically by SQL Server via the ROLLUP option of the GROUP BY clause. Initially, the ROLLUP option places NULL in each "group" that is being rolled-up since the string "Total" (or any string) won't fit into a column of non-string datatypes. I am guessing that the NULL values were replaced with ZZTotal via ISNULL() when dumping the results into #final.

The current problem is that if you replace the ZZTotal values with Total, then the "Total" rows will possibly not be at the bottom of that grouping, at least not for any groupings that contain strings starting with U..., or Tp..., or Tou..., and so on.

1. The first step is to add an IDENTITY column to the #final table. You do this by changing the query that creates and populates #final by adding IDENTITY(INT, 1, 1) AS [RowNum], as the first column of the SELECT statement.

2. The second step is to change the ISNULL(column, 'ZZTotal') of the query that populates #final to instead be (for Class2, for example):

   IIF(GROUPING(class2) = 0, class2, N'Total for ' + class1) AS [Class2]
   

   Please note the use of "class1", not "class2", at the end of N'Total for ' + class1.

3. The third / final step is to add ORDER BY [RowNum] to the end of the Set @Sql= statement. The final query should be something like:

   SELECT Class1, Class2, Class3 FROM #final ORDER BY [RowNum];
   

Example 1: ROLLUP default behavior

SELECT ss.[name] AS [SchemaName],
       so.[type_desc] AS [ObjectType],
       COUNT(*) AS [ObjectCount]
FROM [master].sys.schemas ss
INNER JOIN [master].sys.objects so
        ON so.[schema_id] = ss.[schema_id]
GROUP BY ss.[name], so.[type_desc] WITH ROLLUP;

Example 2: ROLLUP with NULLs replaced in GROUPING rows

SELECT IIF(GROUPING(ss.[name]) = 0, ss.[name], N'Total') AS [SchemaName],
       IIF(GROUPING(so.[type_desc]) = 0,
           so.[type_desc], 
           IIF(GROUPING(ss.[name]) = 0,
               N'Total for ' COLLATE Latin1_General_CI_AS_KS_WS + ss.[name],
               N'---------------------------------------->')
          ) AS [ObjectType],
       COUNT(*) AS [ObjectCount]
FROM [master].sys.schemas ss
INNER JOIN [master].sys.objects so
        ON so.[schema_id] = ss.[schema_id]
GROUP BY ss.[name], so.[type_desc] WITH ROLLUP;

Example 3: Adding IDENTITY column and INTO clause to Example #2

SELECT IIF(GROUPING(ss.[name]) = 0, ss.[name], N'Total') AS [SchemaName],
       IIF(GROUPING(so.[type_desc]) = 0,
           so.[type_desc], 
           IIF(GROUPING(ss.[name]) = 0,
               N'Total for ' COLLATE Latin1_General_CI_AS_KS_WS + ss.[name],
               N'---------------------------------------->')
          ) AS [ObjectType],
       COUNT(*) AS [ObjectCount],
       IDENTITY(INT, 1, 1) AS [RowNum]
INTO #TempResults
FROM [master].sys.schemas ss
INNER JOIN [master].sys.objects so
        ON so.[schema_id] = ss.[schema_id]
GROUP BY ss.[name], so.[type_desc] WITH ROLLUP;

SELECT * FROM #TempResults ORDER BY [RowNum];