How do I fill a temp table iteratively and stuff() the result into columns?

Question

If I want to know for each user how much time they spent on the intranet on a certain day, I can use a custom function - 2 examples:

select * from [dbo].[usertime]('2016-04-08')

userid  totaltime
-----------------
1       4430
2       11043
5       13045


select * from [dbo].[usertime]('2016-04-09')

userid  totaltime
-----------------
1       345
3       12066
9       15344

I have no control over the function and can only use its output. The totaltime is in seconds.

From another table, I can select the dates in a year:

select * from dates;

date
----------
2016-01-01
...
2016-04-08
2016-04-09

I would like to run the custom function usertime for each date in the dates table and store the result in a temp table, as follows:

userid  2016-01-01  ..  2016-04-08  2016-04-09
----------------------------------------------
1       ..              4430        345
2       ..              11043       0
3       ..              0           12066
5       ..              13045       0
9       ..              0           15344

This would require me to call usertime in a loop, pseudo:

create table #usertime
(
    userid  int
    date    date
    seconds int
)

select * into #dates from dates;

foreach (#dates as _date)
    update #usertime with [dbo].[usertime](_date)

select * from #usertime

userid  2016-01-01  ..  2016-04-08  2016-04-09
----------------------------------------------
1       ..              4430        345
2       ..              11043       0
3       ..              0           12066
5       ..              13045       0
9       ..              0           15344

I understand I need dynamic SQL here to loop with a different date every time and stuff() to create multiple columns from rows in the resultset coming from #usertime. But I do not understand on how to use these funcionalities. Could anyone help me along?

Answer 1

There's no need for any looping (something that should almost always be avoided in SQL).

SELECT
    T.userid,
    D._date,
    T.totaltime
FROM
    #dates D   -- Probably no need for a temporary table either...
CROSS APPLY dbo.usertime(D._date) T

If you need to then pivot those results, then you can do that as well.

Answer 2

It's easier to use a permanent table for the dynamic table structure due to temp table scoping. If you must use a #usertime temp table for some reason, you'll need to nest dynamic SQL, which is pretty ugly.

Below is an example of how you can pivot the results from rows to columns dynamically.

SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.TempUserTime', 'U') IS NOT NULL
    DROP TABLE dbo.TempUserTime;
IF OBJECT_ID(N'tempdb..#UnpivitedUserTime', 'U') IS NOT NULL
    DROP TABLE #UnpivitedUserTime;

--load temp table with unpivoted data
SELECT date, userid, totaltime
INTO #UnpivitedUserTime
FROM dates
CROSS APPLY dbo.userTime(date)
WHERE date BETWEEN '2016-01-01' AND '2016-04-09';

--create pivot table structure with userid and one column per date
DECLARE @SQL nvarchar(MAX) = 'CREATE TABLE dbo.TempUserTime(userid int NOT NULL';
SELECT @SQL += ',' + QUOTENAME(CONVERT(char(10), date, 121)) + ' int NULL'
FROM dates
WHERE date BETWEEN '2016-01-01' AND '2016-04-09';
SELECT @SQL += ');'
EXEC(@SQL);

--insert a row into pivot table for each user
INSERT INTO dbo.TempUserTime (userid)
SELECT DISTINCT userid FROM #UnpivitedUserTime;

--generate an update statement for each date to update all users
SET @SQL = N'';
SELECT @SQL += N'UPDATE dbo.TempUserTime
SET ' + QUOTENAME(CONVERT(char(10), date, 121)) + N' = (
    SELECT totaltime
    FROM #UnpivitedUserTime AS u
    WHERE
        u.date = ''' +  + CONVERT(char(10), date, 121) +  + N'''
        AND u.userid = TempUserTime.userid
    );
'
FROM dates
CROSS APPLY dbo.userTime(date)
WHERE date BETWEEN '2016-01-01' AND '2016-04-09';

--execute update batch
EXEC(@SQL);

--return results
SELECT *
FROM dbo.TempUserTime
ORDER BY userid;

IF OBJECT_ID(N'dbo.TempUserTime', 'U') IS NOT NULL
    DROP TABLE dbo.TempUserTime;
IF OBJECT_ID(N'tempdb..#UnpivitedUserTime', 'U') IS NOT NULL
    DROP TABLE #UnpivitedUserTime;
GO

Answer 3

As Tom H said, you should avoid looping and you should be able to do this with a cross apply. The Dynamic SQL is to build the columns depending on what you have in the dates table.

DECLARE @SearchList     varchar(1000)
DECLARE @sql            varchar(MAX)

SELECT @SearchList = COALESCE(@SearchList, '') + ',['  + CAST([date] AS VARCHAR(100)) + ']' FROM dates 
select @SearchList

SET @sql = 'SELECT userid' + @SearchList +'
    FROM
    (SELECT d.[date], U.userid, U.totaltime FROM dates d
        CROSS APPLY [dbo].[usertime](d.[date]) U) AS t
    PIVOT
    (
        SUM(seconds)
        FOR [date] IN (' + RIGHT(@SearchList, LEN(@SearchList)-1) + ') 
    ) AS pvt'   

EXEC(@sql)

Answer 4

This looks like you would need a cursor to call your function with the values read from [dates] table. You can start with:

CREATE TABLE #usertime
(
    userid int
    ,date date
    ,seconds int
)
DECLARE @date nvarchar(16)
DECLARE @sql nvarchar(max)
DECLARE curs CURSOR FOR SELECT * FROM dates
OPEN curs
FETCH NEXT FROM curs INTO @date
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'INSERT INTO #usertime SELECT userid,'''+@date+''',totaltime from [dbo].[usertime]('''+@date+''')'
    --print @sql
    exec (@sql)
    FETCH NEXT FROM curs INTO @date
END
CLOSE curs
DEALLOCATE curs

SELECT * FROM #usertime

This should return (unless I have a syntax error with table names) a result like:

userid  date        seconds
----------------------------------------------
1       2016-04-08  4430
1       2016-04-09  345
2       2016-04-08  11043
3       2016-04-09  12066

After this you can add a pivot on that table if you want it pivoted