How can I flatten a table in SQL Server based on values of a column?

Question

I have a table in which every user at each week do some activity, for example user 1 does some activity at week_of = 1, then does some activity at week_of=2 and ...) Number of users are more than 30,000.

I saw this similar question Efficiently convert rows to columns in sql server but my table structure is different with that.

user_id | total_video_time | problem_counts | week_of
--------+------------------+----------------+--------
   1    |      0           |     99         |  1
   2    |    234           |      5         |  1
   1    |   4150           |      9         |  2
   2    |    142           |     16         |  2
   3    |    236           |     40         |  2  
   1    |    649           |     17         |  3
   3    |    500           |     78         |  3
...
...
...
2       |102               |96             |48
3       |147               |43             |48

I want to flat it horizontally based on week_of values to create a view.

can someone help me to write a query for that?

user_id | week_of_1_total_video_time |  week_of_1_problem_counts | .... | week_of_48_total_video_time |  week_of_48_problem_counts
--------|----------------------------|---------------------------|------| ---------------------------|--------------------

[user_id],
[week_of_1_total_video_time],
[week_of_1_problem_counts],
....
.....
.....
[week_of_48_total_video_time],
[week_of_48_problem_counts]

I wrote following code but I got some error:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(week_of) 
                    from [dbo].[View_1]
                    order by week_of
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT user_id,' + @cols + ' from 
             (
                select total_video_time, problem_counts, week_of
                from [dbo].[View_1]
            ) x
            pivot 
            (
                total_video_time, problem_counts
                for week_of in (' + @cols + ')
            ) p '

execute(@query);

my error is:

The number of elements in the select list exceeds the maximum allowed number of 4096 elements.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'x'.

This is a pivot table query. Google: `pivot tables in sql server` — Martin, Nov 15 '19 at 10:11
I have not worked with it, can you guide me how to write its query? — Hossein Ghaffari, Nov 15 '19 at 10:28
I'm afraid that writing the code for you is beyond the scope of what I feel should be provided on SO. Follow the Google search results - there are several great examples of how to achieve this. Once you've tried (and maybe failed), come back with a _specific_ question and I (and others) will be delighted to help — Martin, Nov 15 '19 at 10:29

score 0 · Answer 1 · answered Nov 15 '19 at 10:49

Here is the query fro week 1 and 2. you can add other columns as more as required.

WITH your_table(user_id,total_video_time,problem_counts,week_of)
AS
(
    SELECT 1,0 ,99,1 UNION ALL
    SELECT 2,234,5,1  UNION ALL
    SELECT 1,4150,9,2  UNION ALL
    SELECT 2,142 ,16,2 UNION ALL
    SELECT 3,236 ,40,2   UNION ALL
    SELECT 1,649 ,17,3 UNION ALL
    SELECT 3,500 ,78,3 UNION ALL
    SELECT 2,102,96,48  UNION ALL
    SELECT 3,147,43,48

)

SELECT user_id,

SUM(CASE WHEN week_of = 1 THEN total_video_time ELSE 0 END) week_of_1_total_video_time ,
SUM(CASE WHEN week_of = 1 THEN problem_counts ELSE 0 END) week_of_1_total_problem_counts,

SUM(CASE WHEN week_of = 2 THEN total_video_time ELSE 0 END) week_of_2_total_video_time ,
SUM(CASE WHEN week_of = 2 THEN problem_counts ELSE 0 END) week_of_2_total_problem_counts

FROM your_table
GROUP BY user_id

How can I flatten a table in SQL Server based on values of a column?

1 Answers1