Create a comma separated var-char string in dynamic SQL in one step

Larry Brister
Larry Brister used Ask the Experts™
on
I have dynamic sql that builds a select correctly

DECLARE @DIspo VARCHAR(MAX);
SET @DIspo = '
SELECT ''MAX(cte.'' + COLUMN_NAME + '') AS '' + COLUMN_NAME FROM [tempdb].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE ''#table%'' AND COLUMN_NAME IN (SELECT CODE FROM dbo.DispositionTypes
WHERE DispoReport = 1)';
PRINT @DIspo;

Open in new window


When I copy the Printed @Dispo into a query window I get this

SELECT 'MAX(cte.' + COLUMN_NAME + ') AS ' + COLUMN_NAME FROM [tempdb].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE '#table%' AND COLUMN_NAME IN (SELECT CODE FROM dbo.DispositionTypes
WHERE DispoReport = 1)

When I do run the select
I get three rows in a table

MAX(cte.DNC) AS DNC
MAX(cte.NI) AS NI
MAX(cte.DEALPUR) AS DEALPUR

What I need to do is in ONE process...
Get back a varchar string I can use elsewhere in a dynamic query process

MAX(cte.DNC) AS DNC, MAX(cte.NI) AS NI, MAX(cte.DEALPUR) AS DEALPUR
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You may use several approaches, e.g. store the result into variable:
DECLARE @var varchar(4000)
SET @var = ''

SELECT @var = @var + fld + ', ' FROM ( 
SELECT 'MAX(cte.' + COLUMN_NAME + ') AS ' + COLUMN_NAME fld FROM [tempdb].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE '#table%' AND COLUMN_NAME IN (SELECT CODE FROM dbo.DispositionTypes
WHERE DispoReport = 1)
) rows

SET @var = LEFT(@var, LEN(@var)-2)
SELECT @var

Open in new window

Notice, your command is unchanged just the column name 'fld' was added.
Another approach is to use FOR XML PATH or if you have SQL Server 2017 then you may use STRING_AGG() function:
SELECT STRING_AGG('MAX(cte.' + COLUMN_NAME + ') AS ' + COLUMN_NAME, ', ') FROM [tempdb].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE '#table%' AND COLUMN_NAME IN (SELECT CODE FROM dbo.DispositionTypes
WHERE DispoReport = 1)

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial