PIVOT in Sql server 2005

With the new PIVOT operatorin SQL Server 2005, the CASE statement and GROUP BY statements are no longernecessary. (Every PIVOT query involves an aggregation of some type, so you canomit the GROUP BY statement.) The PIVOT operator provides the samefunctionality that we tried to achieve with the CASE statement query, but youcan achieve it through less code, and it is a bit more pleasing on your eyes. Listing C is a sample PIVOT query tomimic our CASE statement query.

The meat of this query iscontained in the parentheses after the PIVOT operator. Inside the parentheses,we are using a SUM aggregate function to sum up the SalePrice for each Productlisted in the FOR Product IN() statement. This statement may seem somewhat oddbecause the values in the IN() statement are not enclosed in tick (') marks tosignify that they are literal values. These values are actually treated asColumnNames, and they are transformed into tabular fields in the final resultset.

If you run this query, youwill get a result similar to the one from our CASE statement query. The bigdifference is that this query still returns one row for each row listed in ourtable, which is not desirable; we want a distinct list of rows for each year ofsales listed in our table. The problem lies in the way we first constructed ourPIVOT query.

In the SELECT statement inthe query, we simply listed the year and a field for each product listed in ourSalesHistory table. The problem is that the SELECT statement that precedes thePIVOT clauses cannot specify which columns to use in the PIVOT clause. The PIVOT clause applies the grouping to all columns that are not the pivoted or aggregated fields. We can use a subquery to achieve the desired results. Listing D features the resulting query. This small change allows usto mimic our CASE statement query with less code.
Limitations

The PIVOT operator is usefulin many situations, yet there are drawbacks. Much of the functionality providedby the PIVOT operator requires you to "hard code" the fields that youare pivoting into columnar data. You can overcome this by building the PIVOTquery through dynamic TSQL statements, but this is not the most desirablesolution.

Another potential drawback ofusing the PIVOT clause is that you can only aggregrate one field in the resultset; in the CASE statement queries, you can aggregate as many fields as needed.

While these potentialsetbacks may seem like a big deterrent from using the new PIVOT operator, itstill has its place in the TSQL developers' toolbelt.


Example:

First we see a simple output from simple Select Statement


PIVOT



Then We are going to Pivot this table

PIVOT

Nullable Types In C sharp

Why Nullable Types?

Value types are "primitive" data types such as numbers. The .NET Framework stores value types in the stack and implicitly initializes them to their default values—even if you do not explicitly initialize them when they are defined. For example, an int gets initialized to 0, a bool to false, and so on. Unfortunately, most value types are unable to represent a null value, which presents a problem when you're working with data-centric applications where null values are possible, because you have to select some other value to represent null. But whatever value you choose to represent null is no longer available as a valid data value, which restricts the range of permissible values. In other words, if you elect to use -1 to represent null, you've removed -1 as a valid value from the range of numbers that the value type can support. In addition, you must check to ensure that the chosen "null-representation" values are ignored in other parts of the application—and you must make sure not to display the null-representation values directly to end users, who may not understand that the value represents null.

Developers have tried various approaches to solve the null-value type mismatch, but each has its associated pitfalls. This is where nullable types can help. They're perfect for situations where you may need to represent a value type in an undefined state.

The most common such situation occurs when you want to assign the value from a column of a database table (that allows for null values) to a C# type.

The System.Nullable Structure
C# 2.0 provides a System.Nullable generic type struct that you can use to define nullable types. The constructor accepts one parameter—the type itself—and is defined as shown below:

namespace System
{
public struct Nullable : System.IComparable,
System.INullableValue
{
public Nullable(T value);
public static explicit operator T(T? value);
public static implicit operator T?(T value);
public T Value { get; }
public bool HasValue { get; }
public T GetValueOrDefault();
}
}
Here's the syntax you use to define a nullable type:

System.Nullable variable = null;
Note that the generic type T in the preceding code stands for a value type, not a reference type.

As a more concrete example, you can define a nullable integer type as follows:

System.Nullable patientBilledAmount;
The long version above has an equivalent shorthand version:

int? patientBilledAmount;
In C# 2.0, a question mark (?) suffix following a data type designator specifies that the type can accept null values.

If you refer back to the nullable struct definition shown earlier, notice that any nullable type contains two properties that you use in tandem to check for null values. If the type contains a non-null value, the HasValue property returns true; otherwise it returns false. On the other hand, the Value property returns a non-null value if and only if the HasValue property returns true; otherwise, it throws an exception. So typically, you check nullable type values as follows:

if (patientBilledAmount.HasValue)
Console.WriteLine(patientBilledAmount.Value);
else
Console.WriteLine("The amount is null");

Web Form Printing

Print a Web Page Using JavaScript

By Puneet Sharma

with updates by HTMLGoodies Editorial Staff

You've probably been on a web page and wanted to print out the page for later reference. Wouldn't it be great to be able to provide visitors to your site to be able to do just that? This tutorial for web developers will show you how to use JavaScript's window.print function, complete with examples!

The JavaScript window.print() Function

Print is a method of the object "window." At first I thought that the command was simply capturing the buttons along the top of the browser window so all I had to do was substitute "print" with one of the other items, like "mail" or "home". No dice. I also tried setting up the same type of format with items found in the menus like "properties" and "Options." Error after error. It's apparently put into the DTD as a method unto itself.

Saving Grace!

The good thing about the command, the way the authors set it up, is that it does not immediately fire a print. It actually only fires the print window, which still gives your site's visitors a choice in the matter!

It's then up to the person who triggered the method whether to then continue with the printing or not. That's good because I can see a ton of problems with printers not being turned on and huge, huge files being set to print.

So, how to you set up the code? Well, try this first and then look at the code:

Click to Print This Page

And here's the code:

Click to Print This Page

The JavaScript is triggered by using the command "javascript:" in place of a URL, thus allowing a hypertext link to fire off the JavaScript.

And before you ask, yep, this will work with almost any JavaScript Event Handler as long as the format allows you to write it as a specific line or trigger a function.

You can set it to print off of an image:

It looks like this as an image:

And yes, you can set it to trigger off a button:

It looks like this as a form button:

To make sure that your visitors have JavaScript enabled before you provide them with a button that only works using JavaScript, you can use JavaScript to print out the button. That way if they have it disabled, no button appears, saving them the frustration of clicking a button that does absolutely nothing:

Some Printing Suggestions

Okay, now you have the power to force a print request, but that doesn't mean to simply offer a print on any page. You should make a point of being helpful to your users.

• Make a Page for Printing - The Goodies tutorials, as you can see, have a wavy background, a bunch of images, and stuff stuck in all over the place. They're not very good for printing. If I was to offer this page for printing, I would make a point of creating basically a text-only page, or at least a page with limited images and no background.
• Make the Page Printer-Friendly - I would lose all text colors and make sure the width of the page was no more than 500px, left justified, so that what was shown on the screen would print on the printed page in the same way.
• Allow the User to Choose to Print - My print offering would be text at the bottom or an icon that doesn't intrude. Everyone knows they can already print your pages without any help from your coding. So, make this something you offer as a help rather than putting it into people's faces.
• Never Attach This to a User Event - Don't set this to an onLoad, or an onMouseOver, or some other user event. It will only tend to upset those who have visited your page and probably stop them from coming back.