In SQL Server, You can use PIVOT to convert rows into columns.
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
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!