In addition to the ANY clause, you can write a subquery.
You can use the output in any XML parser to produce more useful output. Each value is represented as a name-value element pair. Let us consider the same above example, we will use the pivot statement which will help us store the values in temporary variables.
#Sql server pivot code
Here I will use a slightly modified form of his original code to generate vowels of the English alphabet:Īs you can see, the column STATE_CODE_XML is XMLTYPE, where the root element is. Microsoft SQL Server 2005 introduced the support of pivot statement to get the functionality of pivot and implement the same. Oracle ACE Director Lucas Jellema of Amis Technologies has shown how you can generate rows of specific data for testing purposes. Some of the very interesting uses of unpivot go beyond the usual powerful data manipulation such as the example shown earlier. So unpivot is not for undoing what pivot did, a fact you should carefully consider before creating a pivoted table and then dropping the original one. Instead the crosstab report was displayed in a different way for you to load into a relational table. Later you used unpivot on the table CUST_MATRIX but that didn't get back the details of the original table CUSTOMERS. For instance, in the above example, you created a new table, CUST_MATRIX, using a pivotnoperation on the table CUSTOMERS. Unpivot may be the opposite action of pivot but don't assume that former can reverse what latter has done. As you specified state_counts, that is the name of the new column created in the resultant output. Purchase Frequency New York Conn New Jersey Florida MissouriĪs the column "New York" suddenly became a value in a row, how would you show the value 33048, under which column? That question is answered by the clause just above the for clause inside the unpivot operator in the above query.