Concatenate columns of multiple columns and multiple rows into one varchar value, when no of columns is dynamic

Question

So i have this dynamic query that returns a result set having dynamic number of columns like so:

In this result set we have columns for ID, FacilityName and cycleNum will always be there but number of task columns can vary Task1, Task2, Task3..... upto Taskn.

The final result set I need is as follows:

For this i have tried this query:

        Select distinct FacilityName, 
          substring(
          (
            Select ',' + a   
            From (SELECT ID, FacilityName, 'Cycle-'+ cast(CycleNum as                        varchar)+'::' + 'Task1~' + cast(Task1 as varchar) + ',Task2~' + cast(Task2 as varchar) + ',Task3~' + cast(Task3 as varchar) + ';' as a FROM #tempTable) ST1
            Where ST1.FacilityName = ST2.FacilityName
            ORDER BY ST1.FacilityName
            For XML PATH ('')
        ), 2, 1000) CycleData
From (SELECT ID, FacilityName, 'Cycle-'+ cast(CycleNum as varchar)+'::' + 'Task1~' + cast(Task1 as varchar) + ',Task2~' + cast(Task2 as varchar) + ',Task3~' + cast(Task3 as varchar)+ ';' as a FROM #tempTable) ST2

This will work with following test data:

    create table #tempTable
    (
     ID int,
     FacilityName varchar(50),
     CycleNum int,
     Task1 datetime,
     Task2 datetime,
     Task3 datetime
    )

    Insert into #tempTable values 
    (1, 'A', 1, convert(varchar(10), getdate(), 126), convert(varchar(10),      dateadd(day,1,getdate()), 126), convert(varchar(10), dateadd(day,2,getdate()),  126)),
    (2, 'A', 2, convert(varchar(10), getdate(), 126), convert(varchar(10),    dateadd(day,1,getdate()), 126), convert(varchar(10), dateadd(day,2,getdate()), 126)),
    (3, 'B', 1, convert(varchar(10), getdate(), 126), convert(varchar(10),   dateadd(day,1,getdate()), 126), convert(varchar(10), dateadd(day,2,getdate()), 126)),
    (4, 'B', 2, convert(varchar(10), getdate(), 126), convert(varchar(10),   dateadd(day,1,getdate()), 126), convert(varchar(10), dateadd(day,2,getdate()), 126))

But I cant think of any way to extend this to use dynamic columns. The list of all columns is saved in a master table so we can get comma-separated list of columns from there if needed.

Answer 1

Here is a dynamic way

Slightly different approach from your static query. To make it dynamic, Use while loop or CURSOR to generate the Task1 + Task2 + ..TaskN. Then use it in Select query.

DECLARE @columns VARCHAR(50)='Task1,Task2,Task3', -- Pass the list of column names 
        @int     INT = 1,
        @sql     VARCHAR(8000)

SET @sql = '    ;WITH cte
         AS (SELECT *,
                    ''Cycle-'' + Cast(CycleNum AS VARCHAR(10)) + ''::'' '

WHILE @int <= Len(@columns) - Len(Replace(@columns, ',', '')) -- To find the number of Tasks in list
  BEGIN
      SET @sql += + '+''Task' + Cast(@int AS VARCHAR(10))
                  + '~''+' + 'Cast(Task'
                  + Cast(@int AS VARCHAR(10))
                  + ' AS VARCHAR(50)) + '','''
      SET @int += 1
  END

SET @sql += '   AS concat_dates
             FROM   #tempTable)
    SELECT DISTINCT FacilityName,
                    LEFT(CycleData, Len(CycleData) - 1)
    FROM   cte a
           CROSS apply(SELECT b.concat_dates + '',''
                       FROM   cte b
                       WHERE  a.FacilityName = b.FacilityName
                       FOR xml path('''')) cs (CycleData) 

                       '
--print @sql -- uncomment it to debug if you have any error when executing dynamic code
EXEC (@sql) 

Not to worry about the usage of While Loop/CURSOR since we are not doing any resource intensive operations inside the loop.

Static Query will looking like this

;WITH cte
     AS (SELECT *,
                'Cycle-' + Cast(CycleNum AS VARCHAR(10))
                + '::' + 'Task1~' + Cast(Task1 AS VARCHAR(50))
                + ',' + 'Task2~' + Cast(Task2 AS VARCHAR(50))
                + ',' AS concat_dates
         FROM   #tempTable)
SELECT DISTINCT FacilityName,
                LEFT(CycleData, Len(CycleData) - 1)
FROM   cte a
       CROSS apply(SELECT b.concat_dates + ','
                   FROM   cte b
                   WHERE  a.FacilityName = b.FacilityName
                   FOR xml path('')) cs (CycleData) 

Answer 2

Use system tables. I prefer short and effective solutions :). This is guide, I'm sure you can finish it yourself with concentating strings (if you are not sure, add comment and I will add whole code or more description).

declare @columns varchar(max)
SELECT @columns = isnull(@columns + '+',  '') + '''' + COLUMN_NAME + '~'' + Cast(' + COLUMN_NAME + ' as varchar(50))'
FROM tempdb.INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME like '#tempTable%'
    AND c.COLUMN_NAME LIKE 'Task%' -- filter columns you are interested in

declare @sql varchar(4000) = 'select *, ' + @columns + ' from #tempTable'
print @sql
exec (@sql)