Change columns to rows and vice versa using PIVOT-UNPIVOT

PIVOT

See table below:
TbSummary


Using pivot, we can change rows to columns, so data can be displayed as below:


PIVOT Syntax:
SELECT [new column 1], [new column 2], [new column 3],…
FROM
(
SELECT * FROM [Source Table]
) AS [alias for source table]
PIVOT
(
[AGGREGATE FUNCTION]([value column]) FOR [header column]in ([new column 1], [new column 2], [new column 3],…)
)
AS [alias for pivot table]

Example of query:
SELECT yr, [1], [2], [3]
FROM
(SELECT * FROM TbSummary) AS a
PIVOT
(
SUM(score) FOR batch in ([1], [2], [3])
)
AS b



UNPIVOT

See the tables below:
TbData


In contrary with PIVOT, UNPIVOT is used to change columns to rows. So here, we’re going to display the data as below:


UNPIVOT Syntax:
SELECT [new column 1], [new column 2]
FROM
(
SELECT
[column1],
[column2],
...
FROM [Source Table]
) [alias for source table]
UNPIVOT
(
[new column 2] FOR [new column 1] IN ([column1],[column2],...)
) AS [alias for unpivot table]

Example of the query:
SELECT details, value
FROM
(
SELECT
CAST (id AS varchar) AS id,
CAST ([name] AS varchar) AS [name],
CAST(gender AS varchar) AS gender,
CONVERT(varchar,dob,106) AS dob,
CAST (phonenum AS varchar) AS phonenum
FROM TbData
) a
UNPIVOT
(
value FOR details IN (id, [name], gender, dob, phonenum)
) AS b