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?

Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query). This has been asked and answered hundreds and hundreds of times. — Sean Lange, Apr 08 '16 at 13:46
[dbo].[usertime]('2016-04-08') must recieve also userid. isn't it? — Ruslan K., Apr 08 '16 at 14:11
@RuslanK. - No it must not. The only input is a date. The function retrieves a distinct list of users that were logged in on that day first, and then retrieves the total time spent on the website from another table :) — Pr0no, Apr 08 '16 at 14:26
@Pr0no, sorry for inattention, now I see that function is not scalar but a table-valued. — Ruslan K., Apr 08 '16 at 16:05
Duplicate from same user, slightly altered .... http://stackoverflow.com/questions/36363932/how-can-i-run-my-custom-function-and-query-in-a-loop-for-different-time-frames/36463461#36463461 — Paul, Apr 12 '16 at 11:58

score 13 · Accepted Answer · answered Apr 08 '16 at 14:59

13

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.

answered Apr 08 '16 at 14:59

Tom H

46,766
14
87
128

Nice! However, 'dates' is a permanent table according to the post, not a temporary table. – Jian Huang Apr 14 '16 at 12:44

score 3 · Answer 2 · answered Apr 10 '16 at 19:12

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

score 2 · Answer 3 · answered Apr 15 '16 at 02:38

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)

score 1 · Answer 4 · answered Apr 08 '16 at 14:40

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

score 1 · Answer 5 · answered Apr 13 '16 at 08:20

Hope, I understand your question right!

DECLARE @userstring AS nvarchar(max),
        @sql AS nvarchar(max)

CREATE TABLE #usertime (
    userid int,
    totaltime int,
    dateof date
)

INSERT INTO #usertime VALUES
(1, 4430, '2016-04-08'),
(2, 11043, '2016-04-08'),
(5, 13045, '2016-04-08'),
(1, 345, '2016-04-09'),
(3, 12066, '2016-04-09'),
(9, 15344, '2016-04-09')


SELECT @userstring = stuff((select distinct ',' + quotename(dateof) from  #usertime for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '');

SELECT @sql = '
select *
from (select userid,
             totaltime,
             dateof
        from #usertime) src 
pivot (SUM(totaltime) for [dateof] in ('+@userstring+')
) pvt'

EXECUTE(@sql)

DROP TABLE #usertime

Output:

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

(5 row(s) affected)

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

5 Answers5