Cross Tab queries , Converting Rows to Columns

Hi all,

While browsing on the net I got a really good article by Jeff Moden on Cross Tab queries , Converting Rows to Columns . Hats off to jeff for this beautiful article. Some part of it that I feel was really very helpful for me I am reproducing it here for my future reference and I recommend you guys to read the complete series here

Cross Tab queries , Converting Rows to Columns
Sometimes it is necessary to rotate results so that [the data in] columns are presented horizontally and [the data in] rows are presented vertically. This is known as creating a cross-tab report, or rotating data.

In other words, you can use a Cross Tab or Pivot to convert or transpose information from rows to columns.
A simple introduction to Cross Tabs:

The Cross Tab Report example from Books Online is very simple and easy to understand. I've shamelessly borrowed from it to explain this first shot at a Cross Tab

The Test Data

Basically, the table and data looks like this...

--===== Sample data #1 (#SomeTable1)
--===== Create a test table and some data
CREATE TABLE #SomeTable1
(
Year SMALLINT,
Quarter TINYINT,
Amount DECIMAL(2,1)
)
GO
INSERT INTO #SomeTable1
(Year, Quarter, Amount)
SELECT 2006, 1, 1.1 UNION ALL
SELECT 2006, 2, 1.2 UNION ALL
SELECT 2006, 3, 1.3 UNION ALL
SELECT 2006, 4, 1.4 UNION ALL
SELECT 2007, 1, 2.1 UNION ALL
SELECT 2007, 2, 2.2 UNION ALL
SELECT 2007, 3, 2.3 UNION ALL
SELECT 2007, 4, 2.4 UNION ALL
SELECT 2008, 1, 1.5 UNION ALL
SELECT 2008, 3, 2.3 UNION ALL
SELECT 2008, 4, 1.9
GO

Every row in the code above is unique in that each row contains ALL the information for a given quarter of a given year. Unique data is NOT a requirement for doing Cross Tabs... it just happens to be the condition that the data is in. Also, notice that the 2nd quarter for 2008 is missing.
The goal is to make the data look more like what you would find in a spreadsheet... 1 row for each year with the amounts laid out in columns for each quarter with a grand total for the year. Kind of like this...

... and, notice, we've plugged in a "0" for the missing 2nd quarter of 2008.

Year 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Total
------ ------- ------- ------- ------- -----
2006 1.1 1.2 1.3 1.4 5.0
2007 2.1 2.2 2.3 2.4 9.0
2008 1.5 0.0 2.3 1.9 5.7

The KEY to Cross Tabs!
Let's start out with the most obvious... we want a Total for each year. This isn't required for Cross Tabs, but it will help demonstrate what the key to making a Cross Tab is.
To make the Total, we need to use the SUM aggregate and a GROUP BY
... like this...

--===== Simple sum/total for each year
SELECT Year,
SUM(Amount) AS Total
FROM #SomeTable1
GROUP BY Year
ORDER BY Year


And, that returns the following...

Year Total
------ ----------------------------------------
2006 5.0
2007 9.0
2008 5.7


Not so difficult and really nothing new there. So, how do we "pivot" the data for the Quarter?
Let's do this by the numbers...
1. How many quarters are there per year? Correct, 4.
2. How many columns do we need to show the 4 quarters per year? Correct, 4.
3. How many times do we need the Quarter column to appear in the SELECT list to make it show up 4 times per year? Correct, 4.
4. Now, look at the total column... it gives the GRAND total for each year. What would we have to do to get it to give us, say, the total just for the first quarter for each year? Correct... we need a CASE statement inside the SUM.
Number 4 above is the KEY to doing this Cross Tab... It should be a SUM and it MUST have a CASE to identify the quarter even though each quarter only has 1 value. Yes, if each quarter had more than 1 value, this would still work! If any given quarter is missing, a zero will be substituted.

To emphasize, each column for each quarter is just like the Total column, but it has a CASE statement to trap info only for the correct data for each quarter's column. Here's the code...

--===== Each quarter is just like the total except it has a CASE
-- statement to isolate the amount for each quarter.
SELECT Year,
SUM(CASE WHEN Quarter = 1 THEN Amount ELSE 0 END) AS [1st Qtr],
SUM(CASE WHEN Quarter = 2 THEN Amount ELSE 0 END) AS [2nd Qtr],
SUM(CASE WHEN Quarter = 3 THEN Amount ELSE 0 END) AS [3rd Qtr],
SUM(CASE WHEN Quarter = 4 THEN Amount ELSE 0 END) AS [4th Qtr],
SUM(Amount) AS Total
FROM #SomeTable1
GROUP BY Year

... and that gives us the following result in the text mode (modified so it will fit here)...
Year 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Total
------ ------- ------- ------- ------- -----
2006 1.1 1.2 1.3 1.4 5.0
2007 2.1 2.2 2.3 2.4 9.0
2008 1.5 .0 2.3 1.9 5.7

Also notice... because there is only one value for each quarter, we could have gotten away with using MAX instead of SUM.
For most applications, that's good enough. If it's supposed to represent the final output, we might want to make it a little prettier. The STR function inherently right justifies, so we can use that to make the output a little prettier. Please, no hate mail here! I'll be one of the first that formatting of this nature is supposed to be done in the GUI!

--===== We can use the STR function to right justify data and make it prettier.
-- Note that this should really be done by the GUI or Reporting Tool and
-- not in T-SQL
SELECT Year,
STR(SUM(CASE WHEN Quarter = 1 THEN Amount ELSE 0 END),5,1) AS [1st Qtr],
STR(SUM(CASE WHEN Quarter = 2 THEN Amount ELSE 0 END),5,1) AS [2nd Qtr],
STR(SUM(CASE WHEN Quarter = 3 THEN Amount ELSE 0 END),5,1) AS [3rd Qtr],
STR(SUM(CASE WHEN Quarter = 4 THEN Amount ELSE 0 END),5,1) AS [4th Qtr],
STR(SUM(Amount),5,1) AS Total
FROM #SomeTable1
GROUP BY Year


The code above gives us the final result we were looking for...
Year 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Total
------ ------- ------- ------- ------- -----
2006 1.1 1.2 1.3 1.4 5.0
2007 2.1 2.2 2.3 2.4 9.0
2008 1.5 0.0 2.3 1.9 5.7

What are the main differences between .net 1.1 and .net 2.0

ASP.NET 2.0 introduces a lot of new features. Some of this features aim to simplify the problems faced using the earlier versions and some features are introduced to provide lot of new facilities.
The most important features that are incorporated in ASP.NET 2.0 are:

(a)Master Pages
Master pages are introduced to remove one of the most important deficiencies of earlier version of ASP.NET. One thing that has become apparent in the earlier version of ASP.NET is the lack of architecture for applying a consistent look and feel. In earlier version of ASP.NET whenever a developer wants to replicate a common functionality of a web page in other pages, the most possible options he uses is creating a user control and then replicate the functionality in other pages.

ASP.NET 2.0 aims to solve this problem by introducing the concept of Master pages. First the developer needs to define a master page containing the content that he wants to appear on other pages and then use the ContentPlaceHolder controls to define the locations where the sub pages can plug in the content of their own. The he has to build the sub pages - .aspx pages – that reference the master using directives like this one:

<%@Page MasterPageFile = ~/MyMasterPage.master” %>

In addition, an application can designate a default Master Page in web.config as shown here:







(b)PreCompilation

By default, ASP.NET web pages and code files are compiled dynamically when a first request is made to the page. After the initial compilation, the compiled pages is cached; the cache is used to satisfy the subsequent requests for the same page. Even though this approach is flexible, when the page is requested for the first time, it requires a bit of extra time to compile the code. You can avoid this overhead by leveraging a new feature known as precompilation; by using this feature, you can compile an ASP.NET web site before making the web site available to the users.

(c)Sharing code in the application

In earlier version of ASP.NET, if you were to reference a reusable component from your dot net application, you had to compile the assembly and place it in the bin folder (or place it in the GAC) of the web application. But now with ASP.NET 2.0, creating a reusable component is very simple and straightforward. All you need to do is to create a component in a pre-defined subdirectory called code. Any component placed in this directory will be automatically compiled at runtime into a single assembly. This assembly is automatically referenced and will be available to all the page in the site.

(d)Themes and Skins

ASP.NET 2.0 introduces the concepts of Themes and Skins by means of which the look and feel of the web pages can be enhanced to a great extent to make them visually catchy and attractive.

A skin is a set of visual attributes applied to a control type. A theme is a collection of skins. There are a lot of predefined themes in ASP.NET 2.0. One can use it by using the following line of code:

<%@ Page Theme=”SmokeAndGlass” %>

The page directive’s Them attribute declaratively applies a theme to a page. Themes can also be applied programmatically using the page class’s Theme property

Themes and Skins in ASP.NET 2.0

When you build a Web application, it usually has a similar look and feel across all its pages. Not too many applications are designed with each page dramatically different from the next. Generally, for your applications you use similar fonts, colors, and server control styles across all the pages.

You can apply these common styles individually to each and every server control or object on each page, or you can use a new capability provided by ASP.NET 2.0 to centrally specify these styles. All pages or parts of pages in the application can then access them.

You can read more about this topic..

How to apply themes
Themes can be used by a variety of methods. The following examples show you how you can define a theme named "SmokeAndGlass" in your application by different methods:

<%@ Page Theme="SmokeAndGlass" Language="C#"%>
Themes can be set programmatically under the Page_PreInit event handler. It is necessary to define them in this event handler as all the looks are applied to the server controls defined in a particular theme before the page loads. Here is how you can set themes programmatically:


protected void Page_PreInit(object sender, EventArgs e)
{
string theme = "";
if (Page.Request.Form.Count > 0)
{
theme = Page.Request["Themes"].ToString();
if (theme == "Default")
{
theme = "";
}
}
this.Theme = theme;
}

Set up SQL Server Mode

In this tutorial we are going to focus on setting up the SQL Server method for maintaining session state. Here are the steps for setting this method up on your SQL server:
• Connect to your SQL server using Query Analyzer
• Open the SQL script InstallSqlState.sql script that is normally located in C:\Windows\Microsoft.Net\Framework\[YourVersion]. Replace C: with the drive letter you have yours installed on. This script will create the ASPState database, tables, and stored procedures needed for this method of maintaining session state.
• If using a trusted connection for connecting to your SQL server you must change ownership of the ASPNet database to the 'sa' account. To do this, in Query Analyzer run this
SQL
use ASPNet
exec sp_changedbowner 'sa', 'true'

The sp_changedbowner stored procedure is a system stored procedure that is used to, you guessed it, change the owner of a database.
• If you decide to use SQL Authentication instead of Windows Authentication you need to create a user account for the process. This account must have, at the very minimum, execute rights for all the stored procedures in the ASPNet database.
Now that we have your SQL Server configured for this mode of state management, we now need to configure your Web application to look at your newly created ASPState database. Fortunately doing this is far fewer steps than getting the database ready. These steps are:
• Set the mode of the sessionState element in your web.config to SQLServer.
• Set the sqlConnectionString attribute of the same element to the connection string for your SQL server. The web.config should now look like:
CODE
mode="SQLServer"
sqlConnectionString="data source=YourServer;user id=YourUsername;password=YourPassword"
cookieless="false" timeout="20" />
Now you have your SQL Server ready for this, and your Web application ready, so you would think you're done now right? Not necessarily; Lets say you're running SQL Server 2000 with Service pack 3, if this is the case the first time you run your Web application against your ASPState database you're going to get errors such as:
CODE
SELECT permission denied on object 'ASPStateTempApplications', database 'tempdb', owner 'dbo'.
Why is this you might ask, well Im glad you asked because I have the answer. Service Pack 3 for SQL Server 2000 adds a new secutiry feature, which as you can imagine from the above error message, is disabled by default. This new security feature is Cross DB Ownership Chaining. How nice of Microsoft to inform you of this before you went through all this isn't it.

don't be discouraged, fortunately this can be enabled by running the sp_configure and sp_dboption system stored procedures. First you run the sp_configure stored procedure, like so
SQL
use master
go
EXEC sp_configure 'Cross DB Ownership Chaining', '0';
RECONFIGURE
GO
Then reboot your SQL Server. Once your server is rebooted you will then need to run the sp_dboption stored procedure, like this:
SQL
use master
go
EXEC sp_dboption 'ASPState', 'db chaining', 'true'
go
Once you have completed this final step your SQL Server is now ready to handle session state management. There you have it, setting up session management using SQL Server 2000. In the next tutorial we will look at setting up a state server for session management, until then I hope you found this tutorial informative and useful. Thanks for reading and happy coding!

SQL SERVER 2000 - EXEC master..xp_cmdshell permissions

[xp_cmdshell]
Extended stored procedures are essentially compiled Dynamic Link Libraries (DLLs) that use a SQL Server specific calling convention to run exported functions. They allow SQL Server applications to have access to the full power of C/C++, and are an extremely useful feature. A number of extended stored procedures are built in to SQL Server, and perform various functions such as sending email and interacting with the registry.
xp_cmdshell is a built-in extended stored procedure that allows the execution of arbitrary command lines. For example:
exec master..xp_cmdshell 'dir'

TO execute any EXE from your stored procedure then you will need to use
XP_cmdshell command. It will successfully execute the exe.

Syntax of cmd shell:-

DECLARE @CMDPATH VARCHAR (500)
SET CMDPATH= 'exepath.exe ' +convert(varchar, @param1) + ' ' + convert(varchar,@param2)
EXEC master ..XP_CMDSHELL @CMD

Just try it....

select one radio button which are in datalist in asp.net

ASPX Page Code--------->

javascript tag start from here---
function CheckOnes(chkControl)
{
var oItem = chkControl.children;
var theBox= (chkControl.type=="radio") ? chkControl : chkControl.children.item[0];

xState=theBox.unchecked;
elm=theBox.form.elements;

for(i=0;i

Data list code------------->
within datalist use server side checkbox
DataList ID="lstTemplates" runat="server" OnItemDataBound="lstTemplates_ItemDataBound"/
ItemTemplate/ asp:RadioButton ID="rdbTemplate" runat="server" /ItemTemplate
/DataList

Server side code for DataList-------------->

protected void lstTemplates_ItemDataBound(object sender, DataListItemEventArgs e)
{
RadioButton rdb;
rdb = (RadioButton)e.Item.FindControl(" rdbTemplate?);>if (rdb != null)
{
rdb.Attributes.Add("onclick", "CheckOnes(this);");
}
}

Check and Uncheck All Checkboxes in a DataList using ASP.NET and C#

Here's the client-side javascript function in the aspx page


function CheckAll(checkAllBox, fieldName)
{
var frm = document.Form1;
var actVar = checkAllBox.checked;
for(i = 0; i < frm.length; i++)
{
e = frm.elements[i];
if (e.type == 'checkbox' && e.name.indexOf(fieldName) != -1)
e.checked = actVar ;
}
}


function UnCheckAll(fieldName)
{
var frm = document.frmAdsKeyword;
for(i = 0; i < frm.length; i++)
{
e = frm.elements[i];
if (e.type == 'checkbox' && e.id.indexOf(fieldName) != -1)
e.checked = false ;
}
return false;
}