Pivot table in SQL Server 2008 - Example

Introduction

A pivot table is a regularly used technique of shortening and displaying particular report information by means of consortium and aggregating values. Pivot tables are easily shaped by office users using Microsoft Excel or MS Access. Sincepivot table enables information builders and BI (Business Intelligence) specialists authorize their appearance of information and amplify the visibility andunderstandabilityof mined information, pivot tables are widespread and favored widely. Pivot tables exhibit information in tabular form. The pivot table formatting is not dissimilar than a tabular report formatting. But the tuples are produced by the statement information itself.

In this example i am using a temp table.

Sample Table

CREATE TABLE #Product(Cust VARCHAR(25), Product VARCHAR(20), QTY INT)

INSERT INTO #Product(Cust, Product, QTY)
VALUES('KATE','VEG',2)

INSERT INTO #Product(Cust, Product, QTY)
VALUES('KATE','SODA',6)
INSERT INTO #Product(Cust, Product, QTY)
VALUES('KATE','MILK',1)
INSERT INTO #Product(Cust, Product, QTY)
VALUES('KATE','BEER',12)
INSERT INTO #Product(Cust, Product, QTY)
VALUES('FRED','MILK',3)
INSERT INTO #Product(Cust, Product, QTY)
VALUES('FRED','BEER',24)
INSERT INTO #Product(Cust, Product, QTY)
VALUES('KATE','VEG',3)



Select * from #Product order by Cust

Cust                      Product              QTY
------------------------- -------------------- -----------
FRED                      MILK                 3
FRED                      BEER                 24
KATE                      VEG                  3
KATE                      VEG                  2
KATE                      SODA                 6
KATE                      MILK                 1
KATE                      BEER                 12

(7 row(s) affected)

Pivot table based on customer field

SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT CUST, PRODUCT, QTY
FROM #Product) up
PIVOT (  SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)  
) AS pvt
ORDER BY CUST

CUST                      VEG         SODA        MILK        BEER        CHIPS
--------- ----------- ----------- ----------- ----------- ----------- ---------
FRED                      NULL        NULL        3           24          NULL
KATE                      5           6           1           12          NULL

(2 row(s) affected)

Pivot table based on product field

SELECT PRODUCT, FRED, KATE
FROM (
SELECT CUST, PRODUCT, QTY
FROM #Product) up
PIVOT (SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt
ORDER BY PRODUCT

PRODUCT              FRED        KATE
-------------------- ----------- -----------
BEER                 24          12
MILK                 3           1
SODA                 NULL        6
VEG                  NULL        5

(4 row(s) affected)

Checking Image resolution using Javascript - Asp.net File Uploader

Script

Download jquery.min.2.0.2.js

  <script src="js/jquery.min2.0.2.js" type="text/javascript"></script>
  <script type="text/javascript">
 function setEvent() {
     $(document.getElementById('<%=flpPhoto.ClientID%>')).change(function() {


         // alert(this.files[0]);
         var reader = new FileReader();
         var image = new Image();
         reader.readAsDataURL(this.files[0]);
         reader.onload = function(_file) {
             image.src = _file.target.result;
             image.onload = function() {
                 var w = this.width;
                 var h = this.height;
                 // alert(w + ' ' + h);
                 if (w != 155 && h != 200) {
                     $(document.getElementById('<%=flpPhoto.ClientID%>')).val('');
                     alert('Invalid Student Photo Size. Please upload photo of resolution 155x200 Pixels.');
                   
                 }

             };
         };
     });
   }

function pageLoad() {
setEvent();
}

  </script>


HTML

 <asp:FileUpload ID="flpPhoto" TabIndex="52" runat="server" />