SQL Server: How to SELECT vertically/ horizontally?

name	maths	science	english	sex
aaa	11	22	33	female
bbb	44	55	66	female
ccc	77	88	99	female

Use UNPIVOT

[sql]
select u.Test2, u.Test3 from student
unpivot
(
Test2
for Test3 in (maths, science, english)
) u;

/*Test2 Test3
11 maths
22 science
33 english
44 maths
55 science
66 english
77 maths
88 science
99 English*/
[/sql]
[sql]
select u.name, u.Test2, u.Test3 from student
unpivot
(
Test2
for Test3 in (maths, science, english)
) u;

/*name Test2 Test3
aaa 11 maths
aaa 22 science
aaa 33 english
bbb 44 maths
bbb 55 science
bbb 66 english
ccc 77 maths
ccc 88 science
ccc 99 english*/
[/sql]

Sample 2

[sql]
select * from table_name
/*id aaa bbb ccc
1 12 22 55
2 33 21 76
3 54 35 27*/
[/sql]
[sql]
SELECT id,
description, reading
FROM
(SELECT id,
element1, element2, element3, element4, element5
FROM table_name
) p
UNPIVOT
(reading FOR description IN (element1, element2, element3, element4, element5)
) AS unpvt

/*id description reading
1 aaa 12
1 bbb 22
1 ccc 55
2 aaa 33
2 bbb 21
2 ccc 76
3 aaa 54
3 bbb 35
3 ccc 27*/
[/sql]

PIVOT – Change vertical result into horizontal result

Example table

[sql]
SELECT * FROM [table_name]
–ID field1 field2
–13 Math 10
–13 Physic 3
–14 Biology 4
–14 History 5
–15 Chemistry 6
[/sql]
[sql]
— Select horizontal table
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ‘,’ + QUOTENAME(field1)
from @tmpTable2
group by field1
FOR XML PATH(”), TYPE
).value(‘.’, ‘NVARCHAR(MAX)’)
,1,1,”)

set @query = ‘SELECT ID,’ + @cols + ‘
from
(
SELECT
ID,
field1,
field2
FROM [table_name]
) x
pivot
(
max(field2)
for field1 in (‘ + @cols + ‘)
) p ‘

execute sp_executesql @query;

— ID Math Physic Biology History Chemistry
— 13 10 3
— 14 4 5
— 15 6
[/sql]