Pivot dynamically depending on two columns

Question

I have the following table which records the duration spent by a user for a chatroom :

user_id | duration         | errorscounts   | week
--------+------------------+----------------+--------
   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

I want to get the result as below :

  user_id | week1ofduration |  week1errorscounts | .... | week(n)ofduration |  week(n)errorscounts

I tried already :

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(t.week) 
            FROM MyTable t
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

        set @query = 'SELECT user_id, ' + @cols + ' from 
            (
                select user_id
                    , duration
                    , errorscounts
                from MyTable
           ) x
            pivot 
            (
                 max(duration)
                for week in (' + @cols + ')
            ) p '


execute(@query)

The problem when I try to pivot another time for the errorcounts I get the following error :

The column name "1" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.

Answer 1

First UNPIVOT the data, then PIVOT it. The playable part is to prepare the final headings.

DROP TABLE IF EXISTS #MyTable;

CREATE TABLE #MyTable 
(
    [user_id] INT
   ,[duration] INT
   ,[errorscounts] INT
   ,[week] INT
);

INSERT INTO #MyTable ([user_id], [duration], [errorscounts], [week])
VALUES (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);

DECLARE @cols AS NVARCHAR(MAX)
       ,@cols_week_headings AS NVARCHAR(MAX)
       ,@cols_duration_headings AS NVARCHAR(MAX)
       ,@query AS NVARCHAR(MAX);


SET @cols = STUFF
(
    (
        SELECT [row_value]
        FROM
        (
            SELECT DISTINCT [week] + 0.1
                           ,',' + QUOTENAME('week' + CAST([week] AS VARCHAR(12)) + 'duration')
            FROM #MyTable
            UNION ALL
            SELECT DISTINCT [week] + 0.2
                           ,',' + QUOTENAME('week' + CAST([week] AS VARCHAR(12)) + 'errorscounts')
            FROM #MyTable
        ) DS ([row_id], [row_value])
        ORDER BY [row_id] + 0.1
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)') 
    ,1
    ,1
    ,''
);


SET @query = N'

SELECT *
FROM
(
    SELECT [user_id]
            ,''week'' + CAST([week] AS VARCHAR(12)) + [column] AS [column]
            ,[value]
    FROM #MyTable
    UNPIVOT
    (
        [value] FOR [column] IN ([duration], [errorscounts])
    ) UNPVT
) DS
PIVOT
(
    MAX([value]) FOR [column] IN (' + @cols + ')
) PVT'


execute(@query)