SQL PIVOT – resulting rows in to columns in sql
In some cases you’ll have to generate cross-tabulation reports to summarize data. Most popular method in these kinds of situations is using a “Select Case” clause. But it’ll be more easier and effective if you use the PIVOT.
PIVOT provides syntax that is simpler and more readable than what may otherwise be specified in a complex series of SELECT...CASE statements.
A common scenario where PIVOT can be useful is when you want to generate cross-tabulation reports to summarize data.
This is how it is done:
The table:
-------------------------------------------------------------------------------------
Result:
-------------------------------------------------------------------------------------
Query:
-------------------------------------------------------------------------------------
SELECT 'Averagevalue' AS Value_Sorted_By_IndividualFund,
[US Vector Equity] as Fund1, [Clean Energy Fd], [Dodge & Cox Intl Stock],[Emerging Growth],[Energy Adm]
FROM
(SELECT IndividualFund, value
FROM Funds) AS SourceTable
PIVOT
(
avg(value)
FOR IndividualFund IN ([US Vector Equity], [Clean Energy Fd], [Dodge & Cox Intl Stock], [Emerging Growth],[Energy Adm])
) AS PivotTable
Query explained:
-------------------------------------------------------------------------------------
In the result set notice that the specific columns returned come from the result of the PIVOT clause and not directly from the table (Funds)! And that PIVOT clause brings up the averages of 'value' column in each IndividualFund record, since it is used in the aggregate function avg. Another thing about PIVOT is that there can be only one aggregate function and column referenced. In this case it's avg(value).
Here the results from the original table are taken to the SourceTable. Then inside the PIVOT clause it sorted the averages against the record items that we have mentioned (in Orange color). by the first select clause in the query we retrieve the rows in the PivotTable as the result.
Important:
-------------------------------------------------------------------------------------
You can use only one aggregate function in PIVOT.
Both aliases on sub query and PIVOT clause is required. (In the case SourceTable and PivotTable)
You can set any alias to the result columns.([US Vector Equity] as Fund1)
-------------------------------------------------------------------------------------
Hope you learned something reading the article.But if not, at least you got this info for free. :)
Happy querying.
PIVOT provides syntax that is simpler and more readable than what may otherwise be specified in a complex series of SELECT...CASE statements.
A common scenario where PIVOT can be useful is when you want to generate cross-tabulation reports to summarize data.
This is how it is done:
The table:
-------------------------------------------------------------------------------------
Result:
-------------------------------------------------------------------------------------
Query:
-------------------------------------------------------------------------------------
SELECT 'Averagevalue' AS Value_Sorted_By_IndividualFund,
[US Vector Equity] as Fund1, [Clean Energy Fd], [Dodge & Cox Intl Stock],[Emerging Growth],[Energy Adm]
FROM
(SELECT IndividualFund, value
FROM Funds) AS SourceTable
PIVOT
(
avg(value)
FOR IndividualFund IN ([US Vector Equity], [Clean Energy Fd], [Dodge & Cox Intl Stock], [Emerging Growth],[Energy Adm])
) AS PivotTable
Query explained:
-------------------------------------------------------------------------------------
In the result set notice that the specific columns returned come from the result of the PIVOT clause and not directly from the table (Funds)! And that PIVOT clause brings up the averages of 'value' column in each IndividualFund record, since it is used in the aggregate function avg. Another thing about PIVOT is that there can be only one aggregate function and column referenced. In this case it's avg(value).
Here the results from the original table are taken to the SourceTable. Then inside the PIVOT clause it sorted the averages against the record items that we have mentioned (in Orange color). by the first select clause in the query we retrieve the rows in the PivotTable as the result.
Important:
-------------------------------------------------------------------------------------
You can use only one aggregate function in PIVOT.
Both aliases on sub query and PIVOT clause is required. (In the case SourceTable and PivotTable)
You can set any alias to the result columns.([US Vector Equity] as Fund1)
-------------------------------------------------------------------------------------
Hope you learned something reading the article.But if not, at least you got this info for free. :)
Happy querying.
posted by udayaanthaya at
10:08 PM
|
0 Comments
