Display resultset in tabular format -- in SQL

Question

I have the following table:

user_id    document_id    date
------------------------------------
1           1             2016-01-01
1           2             2016-01-01
1           3             2016-01-02
2           4             2016-01-01
2           5             2016-01-02
3           6             2016-01-02
3           7             2016-01-02
3           8             2016-01-02
3           9             2016-01-03
3          10             2016-01-03
3          11             2016-01-04
3           9             2016-01-04

Is it possible to - in SQL - get the number of documents viewed, per user per day, in tabular format?

date          user_id_1    user_id_2    user_3
----------------------------------------------
2016-01-01    2            1            0
2016-01-02    1            1            3
2016-01-03    0            0            2
2016-01-04    0            0            2

Normally I would use any scripting language to create this by looping through a resultset, but in this particular case I can't. Because of restrictions, I only have access to SQL server.

If this is even possible, any help is greatly appreciated.

[one step further](https://www.google.co.il/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=sql%20server%20pivot), [two steps further](https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx) — Zohar Peled, Mar 31 '16 at 11:37
Are users limited in the amount? or there can be infinity users? — sagi, Mar 31 '16 at 11:38
you could use pivot if possible dates are limited and known...but i don't think this is the case, isn't it? — alessalessio, Mar 31 '16 at 11:39
You have only access to SQL server, but you can write and run a script, can't you? — alessalessio, Mar 31 '16 at 11:46
Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Henrik Staun Poulsen, Mar 31 '16 at 11:52
@alessalessio SQL scripts, yes. I mean, no other scripts. Usually, I would loop through the resultset in Powershell for instance, and write the table to a file. — Pr0no, Mar 31 '16 at 12:01
I would use GROUP and then create the table in the language I am using it in. If you want a pure SQL solution, the linked article gives you the exact solution. — Siderite Zackwehdex, Mar 31 '16 at 12:05
it will be helpful for you if you post this question in [dba.stackexchange](http://dba.stackexchange.com/). — Sandeep, Mar 31 '16 at 12:22
"The number of dates and users are unlimited" would mean unlimited number of columns, which is impossible. — Excelan, Mar 31 '16 at 12:56

score 1 · Accepted Answer · answered Mar 31 '16 at 12:58

As I don't know how your table is called, so I call it [YourTableNameGoesHere]:

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

SELECT @userstring = stuff((select distinct ',' + quotename([user_id]) from [YourTableNameGoesHere] for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '');
SELECT @columns = '[date],' + stuff((select distinct ',' + quotename([user_id]) + ' as ' + quotename('user_id_'+CAST([user_id] as nvarchar(10))) from [YourTableNameGoesHere] for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '');

SELECT @sql = '
select '+@columns+'
from (select [date],
            [user_id],
            document_id
        from [YourTableNameGoesHere]) src 
pivot (COUNT(document_id) for [user_id] in ('+@userstring+')
) pvt'

EXECUTE(@sql)

The output is:

date       user_id_1   user_id_2   user_id_3
---------- ----------- ----------- -----------
2016-01-01 2           1           0
2016-01-02 1           1           3
2016-01-03 0           0           2
2016-01-04 0           0           2

(4 row(s) affected)

Display resultset in tabular format -- in SQL

1 Answers1