SQL Pivot – Rows To Columns

sql-pivot

In SQL Server, You can use PIVOT to convert rows into columns.

Syntax:

SELECT
    <non-pivoted column>,
    [pivoted column1],
    [pivoted column2],
    ...
FROM
    (SELECT <columns> FROM <source_table>) AS <alias>
PIVOT
(
    <aggregate_function>(<value_column>)
    FOR <pivot_column> IN ([pivoted column1], [pivoted column2], ...)
) AS <pivot_alias>;

<non-pivoted column> are columns which are not intended for pivot
[pivoted column1], [pivoted column2] are columns which we want to pivot
The Select statement in FROM clause is for source table
<aggregate_function> is useful when we have two or more rows with similar primary key

Example:

To Demostrate the use of the PIVOT , we will be using MARKS_DATA table

SELECT *
FROM MARKS_DATA

Now we will pivot the table and convert subject as columns

SELECT
STUDENT_ID,
[MATHS] AS MATHS,
[SCIENCE] AS SCIENCE,
[ENGLISH] AS ENGLISH
FROM 
(SELECT STUDENT_ID, SUBJECT, MARKS FROM MARKS_DATA) AS SOURCE_TABLE
PIVOT(
SUM(MARKS)
FOR SUBJECT IN ([MATHS], [SCIENCE], [ENGLISH])
) AS PIVOT_TABLE;

For STUDENT_ID 1026 all three subjects have NULLs because no data is available for them.

Use of Aggregate Function

For each combination of values in the non-pivoted columns and the pivoted columns, there may be multiple values in the source table. The aggregate function is applied to these values to produce a single, aggregated result for each combination.

Some people find it confusing when they see aggregate function inside pivot function, lets clear that misconception using an example.

Suppose for STUDENT_ID 1001 we add one more row and then apply the PIOVT function, here is how that will go

INSERT INTO MARKS_DATA
VALUES (1001,'English',90);

Now we will Pivot the table again but will use MAX aggregate function instead of SUM

SELECT
STUDENT_ID,
[MATHS] AS MATHS,
[SCIENCE] AS SCIENCE,
[ENGLISH] AS ENGLISH
FROM 
(SELECT STUDENT_ID, SUBJECT, MARKS FROM MARKS_DATA) AS SOURCE_TABLE
PIVOT(
MAX(MARKS)
FOR SUBJECT IN ([MATHS], [SCIENCE], [ENGLISH])
) AS PIVOT_TABLE;

As you can see, now for STUDENT_ID 1001 it displays 90 marks for ENGLISH subject (90>88)

Hope you now understands the use of Aggregate function in Pivot. There are several Aggregate function like SUM, MAX, MIN, AVG that you can uise in Pivot.

Thanks For Reading!

You can also perform Pivot using SQL Case statements, Learn more