Dynamically generate SQL columns from comma-separated string

Data and Databases
1 
I'm working on a SQL Server project and need to create a dynamic query. Here's what I'm trying to do:

I have an input parameter like this:
@inputString = 'column1,column2,column3,'

I want to turn it into an output like this:
MAX(column1) as column1, MAX(column2) as column2, MAX(column3) as column3

I've tried using CONVERT() and REPLACE() functions, but I'm not getting the desired result. I'm still learning SQL and could use some help figuring out the best approach.

Can someone show me a way to split the input string and build the output dynamically? Any suggestions or code samples would be greatly appreciated. Thanks!
2

I’ve encountered a similar challenge in my work. One efficient approach is to utilize a combination of STRING_SPLIT and STRING_AGG functions. Here’s a concise solution:

SELECT STRING_AGG(CONCAT(‘MAX(’, value, ') as ', value), ', ')
FROM STRING_SPLIT(@inputString, ‘,’)
WHERE TRIM(value) <> ‘’

This method splits your input string, filters out empty values, and then aggregates the results into the desired format. It’s clean, performant, and easily adaptable to varying input lengths. I’ve found it particularly useful for dynamic column generation in reporting scenarios.

3

Ooh, interesting problem! have u thought about using a table-valued function? it could split ur string and then u could use CROSS APPLY to generate the columns. might be more flexible for different inputs. wat do u think about that approach? could be fun to explore!

4

hey finn, i’ve dealt with this before. you could try using a combo of STRING_SPLIT and STUFF functions. split the string, then use STUFF to build your dynamic columns. something like:

SELECT STUFF((
SELECT ‘, MAX(’ + value + ‘) as ’ + value
FROM STRING_SPLIT(@inputString, ‘,’)
WHERE value <> ‘’
FOR XML PATH(’')
), 1, 2, ‘’)

hope that helps! lemme know if u need more info.