How to aggregate rows with "CASE WHEN" statement and build query as a string

Question

This is my sample table:

TableName    ColumnName    
Sample       Name 
Sample       MiddleName 
Sample       LastName

I'm trying to test the following code:

SELECT 'SELECT ' +
CASE WHEN TableName IS NOT NULL AND ColumnName IS NOT NULL THEN  TableName_ColumnName 
     WHEN TableName IS NOT NULL AND ColumnName IS NULL THEN TableName_NULL
     ELSE ISNULL(ColumnName, 'NULL') 
END
+ ' FROM [TestDB].[dbo].' + TableName
FROM [TestDB].[dbo].[TestTable] WHERE TableName = 'Sample'

Here is a result I'm getting from the above, it's including the SELECT and 'FROM [TestDB].[dbo]' + TableName for each row which is not what I want:

SELECT  Sample_Name  FROM [TestDB].[dbo].Sample
SELECT Sample_MiddleName  FROM [TestDB].[dbo].Sample
SELECT Sample_LastName   FROM [TestDB].[dbo].Sample

The ideal result should look like this:

SELECT 
Sample_Name  
Sample_MiddleName 
Sample_LastName 
FROM [TestDB].[dbo].Sample

What am I missing?

As per any query you get one result row per input row. You need something to concatenate multiple rows. — Dale K, Nov 18 '20 at 01:18
Google? https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv — Dale K, Nov 18 '20 at 02:40
I don't see any way to get the result you're getting from the sample data you've posted using the query in your question. Please [edit] your question following the guidelines in the [tsql tag info](https://stackoverflow.com/tags/tsql/info) - including proper sample data and version tag. — Zohar Peled, Nov 18 '20 at 08:12

score 1 · Accepted Answer · answered Nov 18 '20 at 20:29

If I understood correctly from your question you need a solution like this:

WITH TableNamesCTE AS
(
    SELECT DISTINCT TableName
    FROM TestTable
)
SELECT 'SELECT ' +
    (SELECT STRING_AGG(CASE WHEN TableName IS NOT NULL AND ColumnName IS NOT NULL THEN  CONCAT(TableName, '_', ColumnName)
                            WHEN TableName IS NOT NULL AND ColumnName IS NULL THEN CONCAT(TableName, '_NULL')
                            ELSE ISNULL(ColumnName, 'NULL')
                            END, ',')
     FROM [TestTable] TT
     WHERE TT.TableName = TN.TableName)
+ ' FROM [TestDB].[dbo].' + TableName
FROM TableNamesCTE TN
WHERE TN.TableName = 'Sample';

I have separated table names with CTE, so if you have 100 tables, then this query will produce 100 query strings. Once you have all tables prepared then in the second part of the query you will get aggregated all these things.

SQL Server command STRING_AGG is available from 2017 version. For more information: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15

How to aggregate rows with "CASE WHEN" statement and build query as a string

1 Answers1