Unpivoting Multiple Columns
Unpivoting is the process of normalizing data. In earlier post I discussed unpivoting a single column. This demonstration is to show how to unpivot multiple columns. The task is to normalize the following denormalized data, which represents product sales volumes by quarter: product_nbr qtr1 sales_qtr1 qtr2 sales_qtr2 qtr3 sales_qtr3 qtr4 sales_qtr4———– —— ———– —— ———– —— ———– —— ———–1 2008Q1 100 2008Q2 20 2008Q3 15 2008Q4 102 2008Q1 80 2008Q2 15 2008Q3 20 2008Q4 103 2008Q1 70 2008Q2 5 2008Q3 10 2008Q4 15 Normalized data set should look like this: product_nbr qtr sales———– —— ———–1 2008Q1 1001 2008Q2 201 2008Q3 151 2008Q4 102 2008Q1 802 2008Q2 152 2008Q3 202 2008Q4 103 2008Q1 703 2008Q2 53 2008Q3 103 2008Q4 15 The first method uses […]
Pivoting on Multiple Columns
Pivoting data on more than one column is not a common request. But there are times when it is a very useful technique for reporting purposes. Here is one example to demonstrate different methods to handle that. The goal is to report product data by pivoting columns with value and quantity for each product based […]
Dynamic Pivoting
SQL Server 2005 added the PIVOT operator which makes creating cross-tab queries very easy. However, as of this writing none of the SQL Server versions has built-in support for dynamic pivoting. The PIVOT operator only provides basic pivoting capabilities on a static list of values. In practice it is very often needed to perform this […]
How to extract IDENTITY_SEED and IDENTITY_INCREMENT from autoval column in syscolumns
There are many hints on the internet that in the autoval column in the system table syscolumns in MS SQL Server 2000 it is stored the identity seed and identity increment values for columns that have been set as identity. Problem is there are little clues on how to extract those values from that column, […]
Simple Tile Horizontally / Tile Vertically functionality in WPF
Here is the main idea: We will have a grid with three rows and three columns. The middle column/row will be used to store the GridSplitter that will allow us to resize columns/rows once we are in one of the tile modes. So basically when we are in Tile Horizontally we will span the textboxes […]
Import XML File to SQL Table
Here is a brief example of importing an XML file into SQL Server table. This is accomplished by using the BULK option of OPENROWSET to load the file, and then utilizing the XQuery capabilities of SQL Server to parse the XML to normalized table format. This example requires SQL server 2005 or SQL Server 2008. […]