Dynamically generate columns in SQL Server

Question

I have following piece of SQL. I use this to return the daily reception times of an account group.

SELECT 
    ACCT_GRP_ID,
    CONCAT(CONCAT(FORMAT(GETDATE(),'dd-MM-yyyy'), ' '),    
    AS_RECEPTION_TIMES.RECEPTION_WINDOW_START) AS RECEPTION_START,
    CONCAT(CONCAT(FORMAT(GETDATE(),'dd-MMyyyy'), ' '), 
    AS_RECEPTION_TIMES.RECEPTION_WINDOW_END) AS RECEPTION_END
FROM 
    AS_RECEPTION_TIMES
JOIN 
    AS_AS_RECEPTION_CONF ON AS_RECEPTION_TIMES.ACCT_STAT_RECEPTION_CONFIG_ID = AS_AS_RECEPTION_CONF.ID
WHERE 
    ACCT_GRP_ID > 1

This returns the following result:

As you can see I have different account groups with multiple reception timings. Now I want to group accounts so I only have 1 line for each ACCT_GRP_ID with all the reception start and endings in columns behind it.

How can I realize this?

Answer 1

1 Static solution

If you know in advance how many columns (RECEPTION_START/RECEPTION_END pairs) will be present in your data, then you can generate all column/values combinations and build the final table with a simple PIVOT.

These are the test data I used:

This is the code:

--temporary table with sample data
create table #tmp (ACCT_GRP_ID  int, RECEPTION_START DATETIME, RECEPTION_END DATETIME)

--populate test data
insert into #tmp values (26, '20170725 00:09:00', '20170725 00:09:15'),(26, '20170725 00:09:15', '20170725 00:09:30'),(26, '20170725 00:09:30', '20170725 00:09:45'),(26, '20170725 00:09:45', '20170725 00:10:00'),(27, '20170725 00:15:00', '20170725 00:15:30'),(27, '20170725 00:15:30', '20170725 00:16:00')

--create the new combinations of columns and values and then pivot the data
select * from (
    select tt.ACCT_GRP_ID, tt.col, tt.val from(
        select *, 'RECEPTION_START_' + cast(t.ID as nvarchar(max)) as col ,RECEPTION_START as val FROM (SELECT * , ROW_NUMBER() OVER ( PARTITION  by ACCT_GRP_ID  Order by RECEPTION_START  )   AS ID FROM #tmp) t
        union all
        select *, 'RECEPTION_END_' + cast(t.ID as nvarchar(max)) as col ,RECEPTION_END as val FROM (SELECT * , ROW_NUMBER() OVER (PARTITION  by ACCT_GRP_ID Order by RECEPTION_START) AS ID FROM #tmp) t
     ) tt
 ) ttt
PIVOT ( max(val) for Col in 
    (
        [RECEPTION_START_1], 
        [RECEPTION_END_1],
        [RECEPTION_START_2], 
        [RECEPTION_END_2],
        [RECEPTION_START_3],
        [RECEPTION_END_3],
        [RECEPTION_START_4],
        [RECEPTION_END_4]
    ) 
) AS pvt

Outputs these results:

2 Dynamic solution

If you prefer a dynamic solution that handles an arbitrary number of RECEPTION_START/RECEPTION_END columns you can use dynamic sql. Please note that this solution may cause problems such as sql injection, performance issues and all the cons usually associated with dynamic sql.

In this second solution I altered the first two rows of test data setting row 1 with ACCT_GRP_ID = 24 and row 2 with ACCT_GRP_ID = 25

This change shows that the number of columns in the results is different (there will be two RECEPTION_START/RECEPTION_END pairs instead of 4):

declare @max_columns int
declare @counter int
declare @header nvarchar(max)=''
declare @query nvarchar(max)=''

--temporary table with sample data
create table #tmp (ACCT_GRP_ID  int, RECEPTION_START DATETIME, RECEPTION_END DATETIME)
--temporary table with new column information
create table #metadata (ACCT_GRP_ID  int, col nvarchar(max), [val] datetime, [id] int)

--populate test data
insert into #tmp values (24, '20170725 00:09:00', '20170725 00:09:15'),(25, '20170725 00:09:15', '20170725 00:09:30'),(26, '20170725 00:09:30', '20170725 00:09:45'),(26, '20170725 00:09:45', '20170725 00:10:00'),(27, '20170725 00:15:00', '20170725 00:15:30'),(27, '20170725 00:15:30', '20170725 00:16:00')

--create the new combinations of columns and values
insert into #metadata
    select ACCT_GRP_ID,  'RECEPTION_START_' + cast(t.ID as nvarchar(max)) as col ,RECEPTION_START as val, t.ID FROM (SELECT * , ROW_NUMBER() OVER ( PARTITION  by ACCT_GRP_ID  Order by RECEPTION_START  )   AS ID FROM #tmp) t
    union all
    select ACCT_GRP_ID,  'RECEPTION_END_' + cast(t.ID as nvarchar(max)) as col ,RECEPTION_END as val, t.ID FROM (SELECT * , ROW_NUMBER() OVER (PARTITION  by ACCT_GRP_ID Order by RECEPTION_START) AS ID FROM #tmp) t

--calculate the number of columns of the final table (the maximum number of RECEPTION_START/RECEPTION_END pairs)
select @max_columns= max(m.tot) from(
select  COUNT(*)/2 as tot from #metadata group by ACCT_GRP_ID
 ) m

-- generate the list of columns that will be used for pivoting
set @counter=1
while @counter <= @max_columns
    begin
        set @header += 'RECEPTION_START_' + cast(@counter as nvarchar(50)) + ', RECEPTION_END_' + cast(@counter as nvarchar(50)) + ', '
        set @counter = @counter + 1
    end

    --remove last unnecessary comma
set @header = SUBSTRING(@header,1,LEN(@header)-1)

--build dynamic query  
set @query += ' select * from ('
set @query += '     select ACCT_GRP_ID, col, val from #metadata '
set @query += '  ) tmp'
set @query += ' PIVOT ( max(val) for Col in ('
set @query += @header
set @query += '     )) AS pvt'

--execute dynamic query
exec sp_executesql @query 

This is the result set: