SQL row and column conversions

"One" Row to column

1. Query the original data

/*** the theme of this exercise, row to column, the column change ***/
SELECT * FROM Scores

2, get name, through GROUP by

Select Student as ' name '
From Scores
GROUP BY Student
ORDER BY Student

3, plus Max, Case......when.

Select Student as ' name ',
Max (case Subject "language" then score else 0 end) as ' language ',--if this line is "Chinese", select this row as the column
Max (case Subject when ' English ' then score else 0 end) as ' English '
From Scores
GROUP BY Student
ORDER BY Student

When looking at other materials, see another way to use pivot

--group by, Avg/max, pivot. Here with Max and AVG, the results are the same, what's the difference? I'm not sure.

--refer to the information on the Internet and use the following

/*

Pivot

Aggregate function (column name to be converted to a column value)

For columns to convert

In (target column name)

)

*/

Select Student as ' name ',

AVG (language) as ' language ',
AVG (English) as ' English '
From Scores
Pivot
    
    In (Chinese, English)
    ) as Newscores
GROUP BY Student
ORDER BY Student ASC

SQL row and column conversions