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

 

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]

 
 
Advertisements

Get DetailsView’s page through primary key

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?

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

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

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

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)

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