The method of converting SQL Server longitudinal table and transverse table _mssql

1, the vertical table to the horizontal table

Vertical table Structure Table_a:

Converted structure:

SQL example for vertical table to horizontal table:

SELECT Name,
sum (case when Course = n ' language ' THEN Grade
ELSE 0
) as Chinese,
sum (case when Course = N ' Mathematics ' THEN Grade
else 0 '
as Mathematics,
SUM (case when Course = N ' English ' THEN Grade
ELSE 0 end
) as Engl Ish from
dbo. Table_a
GROUP by Name

2, the horizontal table to the longitudinal table

Horizontal table Structure Table_b:

Converted table structure:

SQL example of a horizontal table to the vertical table:

SELECT Name,
' Chinese ' as Course,
Chinese as Score from
dbo. Table_b
UNION All
SELECT Name,
' mathematics ' as Course,
Mathematics as Score from
dbo. Table_b
UNION All
SELECT Name,
' 中文版 ' as Course,
中文版 as Score from
dbo. Table_b
ORDER by Name,

The above is a small series to introduce the SQL Server vertical table and the conversion of the horizontal table to achieve the method, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!