How to Pivot a Numeric Column in T SQL?

Question

I have a table with data like this :

I need to write a T-SQL query to transform the data to make a result like this :

Answer 1

You can use some sort of dynamic query by executing a SQL statement from a string variable

the concept is pretty much as follows:

first, declare the variable

DECLARE @pivotcolumns NVARCHAR(MAX) = '', 
    @sqlquery     NVARCHAR(MAX) = '';

get all the mode_code and store them into a variable @pivotcolumns

SELECT 
    @pivotcolumns+=QUOTENAME(mode_code) + ','
FROM 
    the_table
ORDER BY 
    mode_code;

SET @pivotcolumns = LEFT(@pivotcolumns, LEN(@pivotcolumns) - 1);

if we print the @pivotcolumns it will become something like '0','1','2','...'

after you get all the column, parse them into a string for SQL query

SET @sqlquery ='
SELECT * FROM (
    select mode_code, time_spent
    from the_table) t
PIVOT(
    SUM(time_spent) 
    FOR mode_code IN ('+ @pivotcolumns +')
) AS pivot_table;';

After you finish making a string of an SQL query, then we can execute it using sp execute SQL

EXECUTE sp_executesql @sqlquery;