A CTE, DENSE_RANK (), String Concatenattion and PIVOT Sample

Posted: March 28, 2008 | Author: | Filed under: SQL Server 2005 | Leave a comment
 

A question was posted here "Show the rows containing same values rate as column in html table": http://forums.asp.net/t/1239535.aspx

I came up a solution to use multiple CTEs, String concatenation, and PIVOT to get the result from SQL Server 2005. Just an interesting way to do it in database. There may be better way to do it from from end but that is not the point for this post.

CREATE

TABLE [dbo].[groupDataset$](

[City] [nvarchar]

(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Kg] [int]

NULL,

[Rate_rs] [decimal]

(8, 2) NULL

)

–Go

INSERT [dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Rajkot’, 1, CAST(25.00 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Rajkot’, 2, CAST(30.00 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Rajkot’, 3, CAST(42.50 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Ahemedabad’, 1, CAST(42.50 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Ahemedabad’, 2, CAST(55.00 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Ahemedabad’, 3, CAST(67.50 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Ahemedabad’, 4, CAST(80.00 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Goa’, 1, CAST(90.00 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Goa’, 2, CAST(105.00 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Goa’, 3, CAST(120.00 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Gondal’, 1, CAST(25.00 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Gondal’, 2, CAST(30.00 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Gondal’, 3, CAST(42.50 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Morbi’, 1, CAST(25.00 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Morbi’, 2, CAST(30.00 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Morbi’, 3, CAST(42.50 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Gandhinagar’, 1, CAST(42.50 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Gandhinagar’, 2, CAST(55.00 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Gandhinagar’, 3, CAST(67.50 AS Decimal(8, 2)))

INSERT

[dbo].[groupDataset$] ([City], [Kg], [Rate_rs]) VALUES (N‘Gandhinagar’, 4, CAST(80.00 AS Decimal(8, 2)))

Solution:

 

;WITH mycte

AS

(SELECT City, Kg, Rate_rs,

DENSE_RANK

() OVER (ORDER BY kg, Rate_rs) AS denseRankNum

FROM

groupDataset$

)

,

mycte2

AS

(

SELECT DISTINCT

Cities

= REPLACE(

(

SELECT

City

AS [data()] FROM

mycte t

WHERE

t

.denseRankNum = c.denseRankNum

ORDER BY

denseRankNum

, City

FOR XML PATH (”)), ‘ ‘, ‘,’) , Kg, Rate_rs

FROM

mycte c

)

–SELECT * FROM mycte2 ORDER BY Kg, Rate_rs, cities

—

SELECT Cities, ISNULL(CAST([1] as varchar),‘-‘) as [1kg],

ISNULL

(CAST([2] as varchar),‘-‘) as [2kg],

ISNULL

(CAST([3] as varchar),‘-‘) as [3kg],

ISNULL(CAST([4] as varchar),‘-‘) as [4kg]

/*, ISNULL(CAST([5] as varchar),’-‘) as [5kg]

, ISNULL(CAST([6] as varchar),’-‘) as [6kg]

*/

— you can add more manually

FROM

(SELECT cities, Kg, Rate_rs FROM mycte2) AS t

PIVOT

(MAX(Rate_rs) FOR Kg IN ([1],[2],[3],[4]/*,[5],[6] */)) as pvt

ORDER

BY [1kg]

 
 

Get DetailsView’s page through primary key

Posted: March 25, 2008 | Author: | Filed under: ASP.NET 2 | Leave a comment
I came across this question from this thread http://forums.asp.net/t/1237696.aspx. The asker found a solution at the end. The key part is to use DataRowView assciate with DetailsView. Here is the code in C#

protected void DetailsView1_OnDataBound(object sender, EventArgs e)

{

for (int i = 0; i < DetailsView1.PageCount; i++)

{

if (((DataRowView)DetailsView1.DataItem).DataView[i].Row["Id"].ToString() == "3") // when id=3, which page will be

//if (myDataRowView.DataView[i].Row[0].ToString() == "3")//working

{

DetailsView1.PageIndex = i;

break;

}

}

Alter multiple columns in a table?

Posted: March 24, 2008 | Author: | Filed under: SQL Server | Leave a comment
If you have to alter more than one columns at one time, you’d better redesign the table with your new requirements. By using Alter Table Alter column, you can modify column only one at a time.  If you do want to alter multiple columns, there is a way to work around this. You can drop the columns you want to modify and readd them to the table later with new definition. Here is a sample.

You can drop multiple columns(keep at least one column in your table) and add them back to your table with your new definitions.

— –Drop multiple columns

ALTER

TABLE testTable

DROP

COLUMN column_c1 ,

column_c2

,

column_c3

;

— –Add multiple columns

ALTER

TABLE testTable

Add

column_c1 Varchar(35) null,

column_c2

Varchar(35) null,

column_c3

Varchar(35) null;

 

–http://forums.asp.net/t/1236370.aspx

Save deleted data through OUTPUT clause with Sql Server 2005

Posted: March 24, 2008 | Author: | Filed under: SQL Server 2005 | Leave a comment

You can use OUTPUT clause if you are using Sql Server 2005.

Create a standalone table tblUsersArchive to archive tblUsers with same columns as your original table. (Right click on the table from Management Studio and Script the table as>> Create to>>New Query Edit Window, change the table name and remove the old key and constrains. You can also add a new key here with an identity field. Make sure the archive table does not have triggers or constrains on it. Also no key relationships are defined for all involved columns.

Here is the sample code:

DELETE FROM [tblUsers]

OUTPUT deleted.* INTO tblUsersArchive

WHERE [UserID] = @UserID

http://forums.asp.net/t/1237210.aspx

 

Pass value from DataList to another page

Posted: March 24, 2008 | Author: | Filed under: ASP.NET 2 | 1 Comment

We can send a value from ButtonLink click within a DataList  by appending the value through CommandArgument as querystring. We can use a session variable to send the value without showing it in the URL. In ASP.NET 2.0, we can also use PostBackUrl property of the linkbutton.

Here is a sample code:

 <asp:Label ID="Label1" runat="server" visible="false"></asp:Label>
<asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource1" OnItemCommand="DataList1_OnItemCommand">
<ItemTemplate>
<asp:LinkButton ID="LinkButton1" Runat=server Text="Go To Next>" CommandName="NextPage" CommandArgument='<%# Eval("userID") %>' PostBackUrl="~/Page2.aspx">

 …..

Code:

protected void DataList1_OnItemCommand(object source, DataListCommandEventArgs e)
    {
        if (e.CommandName == "NextPage")
        {
               string key1 = Convert.ToString(e.CommandArgument);
            this.Label1.Text = key1;
            }

    }
Page2: 
public void page_load()
    {
        if (!IsPostBack)
        {
            Label lbl1 = (Label)PreviousPage.FindControl("Label1");
            //Response.Write(lbl1.Text);
              //you can get lbl.Text as  the userID for your SelectParameter        


                //...............         


        }
    }


 


 

Transaction running balance

Posted: March 18, 2008 | Author: | Filed under: SQL Server 2005 | Leave a comment

A question about how to calculate the running balance for deposit and withdrap was asked here (http://forums.asp.net/t/1234890.aspx).

Here is the table definition:

CREATE TABLE [dbo].[MoneyTrans](

[Id] [bigint]

NOT NULL,

[TransDate] [smalldatetime]

NOT NULL,

[TransName] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MoneyTrans_TransName] DEFAULT (‘Deposit’),

[Amount] [money]

NOT NULL

)

And this is one of the solutions I came up with the CTE function:

–SELECT * FROM MoneyTrans

;

WITH mycte

AS

(

SELECT CONVERT(CHAR(10),TransDate,101) as dateOnly,

SUM

(CASE WHEN TransName=‘Deposit’ THEN Amount ELSE 0 END) as TotalDeposits,

SUM

(CASE WHEN TransName=‘Withdraw’ THEN Amount ELSE 0 END) as TotalWithdrawals,

SUM

((CASE WHEN TransName=‘Deposit’ THEN 1 ELSE –1 END)*Amount) as NewAmount FROM MoneyTrans

GROUP

BY CONVERT(CHAR(10),TransDate,101)

)

SELECT a.dateOnly, a.TotalDeposits, a.TotalWithdrawals, RuuningBalance=SUM(b.newAmount) FROM mycte a

CROSS

JOIN mycte b

WHERE

(b.dateOnly<=a.dateOnly)

GROUP

BY a.dateOnly, a.TotalDeposits, a.TotalWithdrawals

Sort Number Column in a custermized way (Use CASE function)

Posted: March 17, 2008 | Author: | Filed under: SQL Server | Leave a comment
A reader was asking how to sort number of days returned from a datetime function in a custermized way: positively first and then negatively after the postive ones. For example: 22,33,45, -11,-21,-300. It would a easy operation by using CASE statement to generate the sorting order:
 

ORDER BY CASE WHEN ending_in>0 THEN 0 ELSE 1 END,

CASE WHEN ending_in>0 THEN ending_in ELSE –1*ending_in END

Here is the original post: http://forums.asp.net/t/1234313.aspx

 

Get gridview’s column value from code

Posted: March 10, 2008 | Author: | Filed under: ASP.NET 2 | Leave a comment

1.If the column is BoundColumn:

GridViewRow gr = btn.NamingContainer as GridViewRow;

string to=gr.Cell(1).Text;

2. If the column is in a TemplateField and the data-binding expression is used directly in the ItemTemplate, the field value is automatically placed in DataBoundLiteral control.

    DataBoundLiteralControl yourColumnLiteral = (DataBoundLiteralControl)selectRow.Cells[1].Controls[0];
    string yourColumn = yourColumnLiteral.Text;


3. If the data is binded to an asp control, for example, in a label, you can use FindControl to get the text from that gridviewrow.

   Label lbl1 = (Label)gr.Cells[2].FindControl("myLabel1");

Or

   Label lbl1 = (Label)gr.FindControl("myLabel1"); 
    string myString = lbl1.Text;

4. Of cource, you can access your datakey(s) from the DataKeyNames collection for your selected row.

int myIntKey = (Int32)GridView1.DataKeys[myGridViewRow.RowIndex]["myIntKey"];

Or

int myIntKey = (Int32)GridView1.DataKeys[myGridViewRow.RowIndex][0];

Or

int myIntKey = (Int32)GridView1.SelectedDataKey[0];

 

Get data from SQL Server with data format and column headers

Posted: March 4, 2008 | Author: | Filed under: Excel 2007 | Leave a comment
From Excel 2007 Data Tab>>From Other Sources>>From SQL Server(first one)>>type server name, I tried (local) and a named remote instance and both worked>>Choose the database of interest>>choose table or view of interest>>click next and add some optional information here>>Click finish and click OK from the new Import Data window>>Done.

GridView Footer’s column merge

Posted: March 3, 2008 | Author: | Filed under: ASP.NET 2 | Leave a comment
Sometimes we need to use colspan to merge a few columns in the footer of GridView to summarize data. I found two ways we can do this. One way is to create a brand new footer for the gridview (turn off the built-in footer) and add just one table cell fot this footerrow; another way is to remove cells from all columns except the first one and column span this first column with the gridview’s column count.
Sample codes are listed below:
1. We can always add our own footer or footers. Turn off the built-in one by setting ShowFooter="false" and here is the code to add your footer:

 Protected Sub GridView1_OnRowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs) Handles GridView1.RowDataBound
 
        If e.Row.RowType = DataControlRowType.Footer Then

            Dim gv1 As GridView = DirectCast(sender, GridView)

            Dim tc As New TableCell()
            tc.Text = "Number of Entries Found: " & gv1.Rows.Count.ToString()
            'tc.Height = Unit.Pixel(10)
            tc.Attributes("ColSpan") = gv1.Columns.Count.ToString()

            Dim gr As New GridViewRow(-1, -1, DataControlRowType.DataRow, DataControlRowState.Normal)
            gr.Cells.Add(tc)

            Dim gvTable As Table = DirectCast(e.Row.Parent, Table)
            gvTable.Controls.Add(gr)

        End If
End Sub
 
C#:
 
protected void GridView1_OnRowDataBound(object sender, GridViewRowEventArgs e) 
{ 
    
    if (e.Row.RowType == DataControlRowType.Footer) { 
        
        GridView gv1 = (GridView)sender; 
        
        TableCell tc = new TableCell(); 
        tc.Text = "Number of Entries Found: " + gv1.Rows.Count.ToString(); 
        //tc.Height = Unit.Pixel(10) 
        tc.Attributes["ColSpan"] = gv1.Columns.Count.ToString(); 
        
        
        
        GridViewRow gr = new GridViewRow(-1, -1, DataControlRowType.DataRow, DataControlRowState.Normal); 
        gr.Cells.Add(tc); 
        
        Table gvTable = (Table)e.Row.Parent; 
        gvTable.Controls.Add(gr); 
        
    } 
    
    
} 


2.


Protected Sub GridView1_OnDataBound(ByVal sender As Object, ByVal e As EventArgs) Handles GridView1.DataBound
Dim gv1 As GridView = DirectCast(sender, GridView)
gv1.ShowFooter = 
True
For i As Integer = gv1.Columns.Count - 1 To 1 Step -1
gv1.FooterRow.Cells.RemoveAt(i)
Next
gv1.FooterRow.Cells(0).ColumnSpan = gv1.Columns.Count
gv1.FooterRow.Cells(0).HorizontalAlign = HorizontalAlign.Center
gv1.FooterRow.Cells(0).Text = 
"Number of Entries Fouud: " & gv1.Rows.Count.ToString()
gv1.FooterRow.Cells(0).Font.Bold = 
True
End Sub
 
C#:
protected void GridView1_OnDataBound(object sender, EventArgs e) 
{ 
GridView gv1 = (GridView)sender; 
gv1.ShowFooter = true; 
for (int i = gv1.Columns.Count - 1; i >= 1; i += -1) { 
gv1.FooterRow.Cells.RemoveAt(i); 
} 
gv1.FooterRow.Cells[0].ColumnSpan = gv1.Columns.Count;
gv1.FooterRow.Cells[0].HorizontalAlign = 
HorizontalAlign.Center;
gv1.FooterRow.Cells[0].Text = 
"Number of Entries Fouud: " + gv1.Rows.Count.ToString();
gv1.FooterRow.Cells[0].Font.Bold = true; 
}