Here is my table named P. Table contains information about some random people who is having different type of telephonic medium to be contacted.
Here is how my table P looks like:
But I need the output in following way as shown in the snippet
Here we can see that Row value of Table P is mirrored as column name and Corresponding values are loaded as its row.
This type output is very to analyze than original table P. Isn't right?
Well To achieve this format of output, we use, PIVOT command in SQL.
Here is the script and out of it in a Snippet:
Here is how it works. The first two lines of the query is used to select what we need in the output. Next PIVOT command is used to change rows to column names where we are going to use some MultiValue functions like MAX, MIN,AVG etc on the column which is needed to be displayed as row followed by FOR command which is used to indicate based on which column type that MultiValue functions should be grouped on.
Here example based on Phone_type, The numbers are classified. IN command is used to let us know based on what values does MultiValue functions should appear as. Here we used HOME, OFFICE, MOBILE and based on these three present in Column PHONE_TYPE, The values of PHONE_NUMBER columns are classified.
Here is how my table P looks like:
But I need the output in following way as shown in the snippet
Here we can see that Row value of Table P is mirrored as column name and Corresponding values are loaded as its row.
This type output is very to analyze than original table P. Isn't right?
Well To achieve this format of output, we use, PIVOT command in SQL.
Here is the script and out of it in a Snippet:
SELECT NAME, HOME,OFFICE, MOBILE FROM
(SELECT * FROM P) T
PIVOT (MAX (PHONE_NUMBER) FOR PHONE_TYPE IN (HOME,OFFICE,MOBILE)) OUTPUT1
ORDER BY NAME
Here is how it works. The first two lines of the query is used to select what we need in the output. Next PIVOT command is used to change rows to column names where we are going to use some MultiValue functions like MAX, MIN,AVG etc on the column which is needed to be displayed as row followed by FOR command which is used to indicate based on which column type that MultiValue functions should be grouped on.
Here example based on Phone_type, The numbers are classified. IN command is used to let us know based on what values does MultiValue functions should appear as. Here we used HOME, OFFICE, MOBILE and based on these three present in Column PHONE_TYPE, The values of PHONE_NUMBER columns are classified.
ReplyDelete<a href="https://intellimindz.com/sap-mm-training-in-chennai/
<a href="https://intellimindz.com/sap-sd-training-in-chennai/
<a href="https://intellimindz.com/sap-fico-training-in-chennai/
<a href="https://intellimindz.com/sap-ariba-training-in-chennai/
<a href="https://intellimindz.com/sap-abap-training-in-chennai/
<a href="https://intellimindz.com/sap-hr-training-in-chennai/
<a href="https://intellimindz.com/sap-hana-training-in-chennai/
<a href="https://intellimindz.com/sap-scm-training-in-chennai/
<a href="https://intellimindz.com/sap-bo-training-in-chennai/
<a href="https://intellimindz.com/sap-pp-training-in-chennai