28 Sept 2011

Pivot and Unpivot in SQL Server

SELECT Company,A,B
FROM   (SELECT 'CG1' Company,'A' Product,1 Quantity
        UNION
        SELECT 'CG1','B',2
        UNION
        SELECT 'CG1','A',3
        UNION
        SELECT 'CG1','B',4
        UNION
        SELECT 'CG2','A',1
        UNION
        SELECT 'CG2','B',2
        UNION
        SELECT 'CG2','A',3
        UNION
        SELECT 'CG2','B',4) AS MainTable1
PIVOT (sum(Quantity) FOR Product IN (A, B)) AS PivotTable

-----------------------------------------------------------

SELECT Company,Product,Quantity
FROM   (SELECT 'CG1' Company,1 Product1,2 Product2,3 Product3
        UNION
        SELECT 'CG2',2,3,4
        UNION
        SELECT 'CG3',4,2,5) mainTable
UNPIVOT ( Quantity FOR Product IN (Product1, Product2, Product3) ) AS unpivottable
at No comments:

27 Sept 2011

Pivot Table in SQL Server

I have a data like this:

Employee No Training Date
C11 .Net 1/1/2008
C11 Java 1/2/2008
C11 SQL Server 1/3/2008
C12 .Net 1/4/2008
C12 Java 1/5/2008
C13 SQL Server 1/6/2008

Would like the output like this:

Employee No Training Date Training Date Training Date
C11 .Net 1/1/2008 Java 1/2/2008 SQL Server 1/3/2008
C12 .Net 1/4/2008 Java 1/5/2008    
C13 SQL Server 1/6/2008        

One Employee can have n number of Trainings.

Code Snippet
Create TABLE #Table
(EmployeeID int,
Training varchar(50),
DTE datetime)
INSERT INTO #Table Values(11,'.Net', '1/1/2008')
INSERT INTO #Table Values(11,'Java', '1/2/2008')
INSERT INTO #Table Values(11,'SQL', '1/3/2008')
INSERT INTO #Table Values(12,'.Net', '1/4/2008')
INSERT INTO #Table Values(12,'Java', '1/5/2008')
INSERT INTO #Table Values(13,'SQL', '1/6/2008')

DECLARE @listCol VARCHAR(2000)
SELECT @listCol = STUFF(( SELECT DISTINCT
'],[' + ltrim(Training)
FROM #Table
ORDER BY '],[' + ltrim(Training)
FOR XML PATH('')
), 1, 2, '') + ']'

Print @listCol
DECLARE @query VARCHAR(4000)
SET @query =
'SELECT * FROM
(SELECT EmployeeID, Training
FROM #Table
) src
PIVOT (max(Training) FOR Training
IN ('+@listCol+')) AS pvt'
print @query
EXECUTE (@query)
DECLARE @query2 VARCHAR(4000)
SET @query2 =
'SELECT * FROM
(SELECT EmployeeID, Training, DTE
FROM #Table
) src
PIVOT (max(DTE) FOR Training
IN ('+@listCol+')) AS pvt'
print @query2
EXECUTE (@query2)
DECLARE @query3 VARCHAR(4000)
SET @query3 =
'SELECT * FROM (
SELECT * FROM
(SELECT EmployeeID, Training
FROM #Table
) src
PIVOT (max(Training) FOR Training
IN ('+@listCol+')) AS pvt
) A
JOIN
(
SELECT * FROM
(SELECT EmployeeID, Training, DTE
FROM #Table
) src
PIVOT (max(DTE) FOR Training
IN ('+@listCol+')) AS pvt
) B
ON A.EmployeeID = B.EmployeeID
'
print @query3
EXECUTE (@query3)
at No comments:

Pivot Table in SQL with dynamic columns

declare @columns varchar(max)
declare @convert varchar(max)
select @columns= STUFF((select '],[' + substring(SVQ_questiondesc,1,128) from TSurveyQuestions where SVQ_Svsrno = 25
order by  SVQ_Svorderno
for XML path('')),1,2,'') + ']'
print @columns

set @convert = 'select * from (select SVQ_Svsrno,SVQ_Svorderno,substring(SVQ_questiondesc,1,128) as SVQ_questiondesc from TSurveyQuestions where SVQ_Svsrno = 25) SQ
pivot( sum(SVQ_Svorderno) for SVQ_questiondesc in ('+ @columns +')) as pivottable
'
exec (@convert)
at No comments:

26 Sept 2011

Reorder list using jQuery and ASP.NET


In this article I will explain how to reorder list using jQuery and ASP.NET and the saving the item's updated order in the database. The advantage of reordering list with jQuery is that it is light weight, saving of bandwidth and good from user prespective. I have used jQuery UI Sortable List to sort the items. The changes are persisted to database via ajax request. This way of reordering is very interactive as the items are reordered via Drag and Drop and changes are preserved without PostBack which gives a very smooth cross browser experience.






Below is the screenshot how the sequence of the items is sent in the form of comma separated string to the server via ajax request.



DB part:

Let's create a table and name it Items with the three columns ItemID, ItemName, ItemOrder. ItemOrder will contain the sequence number of the item i.e. item's order number. I have inserted few sample records to have some data in our table. Below is the script for it:

Table:
GO
CREATE TABLE [Items]
    (
      [ItemID] INT IDENTITY ,
      [ItemName] VARCHAR(50) ,
      [ItemOrder] INT
    )
GO
INSERT  INTO [Items]
        SELECT  'Item1' ,
                1
INSERT  INTO [Items]
        SELECT  'Item2' ,
                2
INSERT  INTO [Items]
        SELECT  'Item3' ,
                3
INSERT  INTO [Items]
        SELECT  'Item4' ,
                4
INSERT  INTO [Items]
        SELECT  'Item5' ,
                5
INSERT  INTO [Items]
        SELECT  'Item6' ,
                6
INSERT  INTO [Items]
        SELECT  'Item7' ,
                7
INSERT  INTO [Items]
        SELECT  'Item8' ,
                8
INSERT  INTO [Items]
        SELECT  'Item9' ,
                9
INSERT  INTO [Items]
        SELECT  'Item10' ,
                10 
GO
Now to show the items in front end we have to make a select SP. This SP will give the records present inside the Items table. The records will be represented in ascending order based on ItemOrder column. Below is the script for it:

Select SP:
GO
CREATE PROCEDURE [SelectItems]
AS
    BEGIN
        SET NOCOUNT ON
         
        SELECT  [ItemID] ,
                [ItemName]
        FROM    [Items]
        ORDER BY [ItemOrder] ASC
             
        SET NOCOUNT OFF            
    END
GO
We have to make a Update SP too which will come in action when the user will reorder the list from front end. This SP will accept one argument ItemOrder. This argument contains items order in a comma separated form (1,4,5,6,7,8..). Below is the script: 

Update SP
GO
CREATE PROCEDURE [UpdateItemsOrder]
    @ItemOrder VARCHAR(255)
AS
    BEGIN
    
    
        SET NOCOUNT ON     
        DECLARE @Temp TABLE
            (
              [ItemOrder] INT IDENTITY ,
              [ItemID] INT
            )
        INSERT  INTO @Temp
                SELECT  [Value]
                FROM    dbo.SPLIT(@ItemOrder, ',')
        SET NOCOUNT OFF
        UPDATE  [Items]
        SET     [Items].[ItemOrder] = [Temp].[ItemOrder]
        FROM    [Items]
                INNER JOIN @Temp [Temp] ON [Items].[ItemID] = [Temp].[ItemID]
 
    END
GO

Above I have used a split function (dbo.SPLIT) to split the comma separated string passed from outside into a table. Below is the script for it:

Split function:
GO
CREATE FUNCTION [Split]
    (
      @text VARCHAR(MAX) ,
      @delimiter VARCHAR(20) = ' '
    )
RETURNS @Strings TABLE
    (
      [position] INT IDENTITY
                     PRIMARY KEY ,
      [value] VARCHAR(100)
    )
AS
    BEGIN
        DECLARE @index INT
        SET @index = -1
        WHILE ( LEN(@text) > 0 )
            BEGIN -- Find the first delimiter
                SET @index = CHARINDEX(@delimiter, @text)
 
                IF ( @index = 0 )
                    AND ( LEN(@text) > 0 )
                    BEGIN
                        INSERT  INTO @Strings
                        VALUES  ( CAST(@text AS VARCHAR(100)) )
                        BREAK
                    END
 
                IF ( @index > 1 )
                    BEGIN
                        INSERT  INTO @Strings
                        VALUES  ( CAST(LEFT(@text, @index - 1) AS VARCHAR(100)) )
                        SET @text = RIGHT(@text, ( LEN(@text) - @index ))
                    END --Delimiter is 1st position = no @text to insert
                ELSE
                    SET @text = CAST(RIGHT(@text, ( LEN(@text) - @index )) AS VARCHAR(100))
            END
        RETURN
    END
 
GO


Front End Part:

Business class:
Let's make a Business class for Items and name it Item. This class contains the following property ItemID, ItemName and ItemOrder. Below is the code for it:
[Serializable]
public class Item
{
    public int ItemID { get; set; }
    public string ItemName { get; set; }
    public int ItemOrder { get; set; }
}


ASPX code:

Let's come to ASPX part. I have used ListView to show the items in the list. The ListView will be binded with the data fetched from database via SelectItems SP. User will drag the items according to his needs and accordingly changes will be preserved in the database via ajax request made to Sortable.asmx whose code we will discuss later in this article. Below is the code of our ASPX page.

Sample.aspx:
<%@ Page Language="C#" %>
 
<%@ Import Namespace="System.Collections.Generic" %>
<%@ Import Namespace="System.Collections.ObjectModel" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <link rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.8/themes/redmond/jquery-ui.css"
        type="text/css" media="all" />
 
    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js" type="text/javascript"></script>
 
    <script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.8/jquery-ui.min.js"
        type="text/javascript"></script>
 
    <script type="text/javascript">
 
        $(function() {
            $('#sortable').sortable({
                placeholder: 'ui-state-highlight',
                update: OnSortableUpdate
            });
            $('#sortable').disableSelection();
 
            var progressMessage = 'Saving changes... <img src="loading.gif"/>';
            var successMessage = 'Saved successfully!';
            var errorMessage = 'There was some error in processing your request';
            var messageContainer = $('#message').find('p');
 
            function OnSortableUpdate(event, ui) {
                var order = $('#sortable').sortable('toArray').join(',').replace(/id_/gi, '')
                //console.info(order);
                 
                messageContainer.html(progressMessage);
                 
                $.ajax({
                    type: 'POST',
                    url: 'Sortable.asmx/UpdateItemsOrder',
                    data: '{itemOrder: \'' + order + '\'}',
                    contentType: 'application/json; charset=utf-8',
                    dataType: 'json',
                    success: OnSortableUpdateSuccess,
                    error: OnSortableUpdateError
                });
            }
 
            function OnSortableUpdateSuccess(response) {
                if (response != null && response.d != null) {
                    var data = response.d;
                    if (data == true) {
                        messageContainer.html(successMessage);
                    }
                    else {
                        messageContainer.html(errorMessage);
                    }
                    //console.info(data);
                }
            }
 
            function OnSortableUpdateError(xhr, ajaxOptions, thrownError) {
                messageContainer.html(errorMessage);
            }
 
        });
 
    </script>
 
    <style type="text/css">
        #sortable
        {
            list-style-type: none;
            margin: 0;
            padding: 0;
            width: 400px;
        }
        #sortable li
        {
            margin: 0 5px 5px 5px;
            padding: 5px;
            font-size: 1.2em;
            height: 1.5em;
            cursor: move;
        }
        html > body #sortable li
        {
            height: 1.5em;
            line-height: 1.2em;
        }
         
    </style>
</head>
<body>
    <form id="form1" runat="server">
     
    <div class="ui-widget" id="message">
        <div class="ui-state-highlight ui-corner-all" style="margin-top: 20px; padding: 0 .7em;">
            <p>
                Reorder Items
            </p>
        </div>
    </div>
     
     
 
    <ul id="sortable">
        <asp:ListView ID="ItemsListView" runat="server" ItemPlaceholderID="myItemPlaceHolder">
            <LayoutTemplate>
            </LayoutTemplate>
            <LayoutTemplate>
                <asp:PlaceHolder ID="myItemPlaceHolder" runat="server"></asp:PlaceHolder>
            </LayoutTemplate>
            <ItemTemplate>
                <li class="ui-state-default" id='id_<%# Eval("ItemID") %>'>
                    <%# Eval("ItemName") %></li>
            </ItemTemplate>
        </asp:ListView>
    </ul>
    </form>
</body>
</html>
 
<script runat="server">
 
    protected void Page_Load(object sender, EventArgs e)
    {
        ItemsListView.DataSource = FindItems();
        ItemsListView.DataBind();
 
    }
 
    public static IEnumerable<Item> FindItems()
    {
        Collection<Item> items = new Collection<Item>();
        string connectionString =
        "Data Source=YourServerName; Initial Catalog=YourDatabase; User ID=YourUserName; Password=YourPassword";
         
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = connection;
                command.CommandText = "SelectItems";
                command.CommandType = CommandType.StoredProcedure;
 
                connection.Open();
                using (SqlDataReader dataReader = command.ExecuteReader())
                {
                    Item item;
                    while (dataReader.Read())
                    {
                        item = new Item();
                        item.ItemID = (int)dataReader["ItemID"];
                        item.ItemName = Convert.ToString(dataReader["ItemName"]);
                        items.Add(item);
                    }
                }
            }
        }
        return items;
    }
</script>

WebService Code:

Let's come to webservice code which will be hit by the client side to update the item's updated order in the database. In this webservice the UpdateItemsOrder method is written which will accept one argument which is the updated sequence from the client's item's list. This method will hit the the DB and call the UpdateItemsOrder SP and accordingly response will be returned whether the rows are updated or not. Below is the code for it:

Sortable.asmx:
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;
 
 
/// <summary>
/// Summary description for Sortable
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
[System.Web.Script.Services.ScriptService]
public class Sortable : System.Web.Services.WebService
{
 
 
    [WebMethod]
    public bool UpdateItemsOrder(string itemOrder)
    {
        Collection<Item> items = new Collection<Item>();
        string connectionString =
                "Data Source=YourServerName; Initial Catalog=YourDatabase; User ID=YourUserName; Password=YourPassword";
         
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = connection;
                command.CommandText = "UpdateItemsOrder";
                command.CommandType = CommandType.StoredProcedure;
 
                SqlParameter paramUserName = new SqlParameter("@ItemOrder", SqlDbType.VarChar, 255);
                paramUserName.Value = itemOrder;
                command.Parameters.Add(paramUserName);
 
 
                connection.Open();
                return (command.ExecuteNonQuery() > 0);
 
            }
        }
 
    }
}


Below are the screenshots of the FireBug of the requests made to the server.

Screenshot of the Post Data sent to server:




ScreenShot of the Response Data received from server:



That's it we are done with reordering list using jQuery and ASP.NET. Above you saw how we can drag and drop the items of the list and can change their positions according to our needs. The changes are preserved via updated sequence sent to the server.

Do let me know your feedback, comments.
at No comments:
Subscribe to: Posts (Atom)