Search

Oct 8, 2008

Finding nth maximum number in SQL Server 2005

This is the frequent requirement for the developer to find the nth max number from the table. It will easy to get it if you are using SQL Server 2005, as its allows us to make the top query variable.

Please read the Flexibility using TOP clause in SQL Server 2005 for more details.

Lets say, we are having student and their marks, and we want the nth max mark. First will see the table structure and will add few data into it.


SET NOCOUNT ON
DECLARE @Marks TABLE
(
StudName VARCHAR(100),
Mark INT
)

INSERT INTO @Marks VALUES('AAAAA', 55)
INSERT INTO @Marks VALUES('BBBBB', 65)
INSERT INTO @Marks VALUES('CCCCC', 59)
INSERT INTO @Marks VALUES('DDDDD', 52)
INSERT INTO @Marks VALUES('FFFFF', 65)
INSERT INTO @Marks VALUES('EEEEE', 95)

SELECT Mark FROM @Marks ORDER BY Mark DESC

Mark
-----------
95
65
65
59
55
52

Now we write the query which allow us to find nth max mark form this list.


DECLARE @Top INT
SET @Top = 2

SELECT MIN(Mark) AS 'Top' FROM(
SELECT DISTINCT TOP (@Top) Mark FROM @Marks ORDER BY Mark DESC) A

Top
-----------
65

@Top is variable; which gives you the ability to fetch Nth max.

Oct 3, 2008

Matching a delimited string against another delimited string

I found this is the require thing in our application, as some one in Group asked for help on this as well as one of the member of asp.net forums asked same thing but in different context.

What generally we need is, in our database one field is having multiple value separated with comma. Lets say I am sailor of Property, consider Home as property and features; a bunch of features will create feature group; so home will contains one of the feature group. So here is the feature group table.

SET NOCOUNT ON
DECLARE @FeatureGroup TABLE
(
PropertyID INT,
Features VARCHAR(MAX)
)

INSERT INTO @FeatureGroup VALUES(1, 'Gym')
INSERT INTO @FeatureGroup VALUES(2, 'Gym, Swimming Pool')
INSERT INTO @FeatureGroup VALUES(3, 'Swimming Pool, Terrace')
INSERT INTO @FeatureGroup VALUES(4, 'Swimming Pool, Terrace, Gym')
INSERT INTO @FeatureGroup VALUES(5, 'Swimming Pool, Gym, Parking')
INSERT INTO @FeatureGroup VALUES(6, 'Swimming Pool, Terrace, Basement')
INSERT INTO @FeatureGroup VALUES(7, 'Swimming Pool, Gym, Terrace, Basement, Parking')
SELECT * FROM @FeatureGroup

Gives following output
---------------------------------------------------
1 Gym
2 Gym, Swimming Pool
3 Swimming Pool, Terrace
4 Swimming Pool, Terrace, Gym
5 Swimming Pool, Gym, Parking
6 Swimming Pool, Terrace, Basement
7 Swimming Pool, Gym, Terrace, Basement, Parking



Lets say we have to search for "Gym, Parking", we should list those property which contains either Gym or Parking. There are two way to achieve this, one is using SPLIT function [while is the user define function] and another is XML.



Using SPLIT Function:



Its user define function which splits the comma separated value to Table variable. In this case we first split our filter using Split function which will return the rows representation of our filter; mean each filter will be in separate row. Lets see the definition of Split function.




CREATE function [dbo].[Split](@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results Table (Item1 nvarchar(100))
As
Begin
DECLARE @Index int
DECLARE @Slice nvarchar(100)

SET @Index = 1

IF @String Is NULL Return

WHILE @Index != 0
BEGIN
SELECT @Index = CharIndex(@Delimiter, @String)
If @Index != 0
SELECT @Slice = LEFT(@String, @Index - 1)
else
SELECT @Slice = @String

INSERT INTO @Results VALUES(RTRIM(LTRIM(@Slice)))

SELECT @String = RIGHT(@String, LEN(@String) - @Index)
IF LEN(@String) = 0 BREAK
END
RETURN
END



Here is the use of Split.




SELECT * FROM [master].[dbo].[Split] ('Gym, Parking', ',')

Item1
--------
Gym
Parking



Now lets write query which uses Split function.




DECLARE @Filter VARCHAR(100)
set @Filter = 'Gym, Parking'

select PropertyId, Features from @FeatureGroup FG WHERE -1 IN (
SELECT CASE WHEN (PATINDEX('%' + Item1 + '%', FG.Features)) > 0 THEN -1 ELSE 0 END
FROM [master].[dbo].[Split] (@Filter, ','))

Here is the output:
-------------------------------------------------------
1 Gym
2 Gym, Swimming Pool
4 Swimming Pool, Terrace, Gym
5 Swimming Pool, Gym, Parking
7 Swimming Pool, Gym, Terrace, Basement, Parking



And now lets do it with XML.




DECLARE @Filter VARCHAR(100)
set @Filter = 'Gym, Parking'

DECLARE @xmlFilter XML
SELECT @xmlFilter = CAST('<i>' + REPLACE(@Filter, ',', '</i><i>') + '</i>' AS XML)

SELECT DISTINCT
PropertyID, Features
FROM @FeatureGroup FG
CROSS JOIN (
SELECT
x.i.value('.', 'VARCHAR(10)') AS filter
FROM @XmlFilter.nodes('//i') x(i)
) b
WHERE PATINDEX('%' + b.filter + '%', features) > 0

OUTPUT:
-------------------------------------------------------
1 Gym
2 Gym, Swimming Pool
5 Swimming Pool, Gym, Parking
7 Swimming Pool, Gym, Terrace, Basement, Parking
4 Swimming Pool, Terrace, Gym



You can find the details of how XML works in here, you can read the TSQL Labs 13 - Matching a delimited string against another delimited string created by Jacob Sebastian

Changing the value of web.config file runtime

There is pretty small and simple code to change the value of key defined in web.config file.

// Get the reference of the configuration file at the root.
Configuration objConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("~");
// Change the value of one key
Response.Write(string.Format("Old value : {0}<br/>", objConfig.AppSettings.Settings["MyKey"].Value));
objConfig.AppSettings.Settings["MyKey"].Value = "changed web config";
// Save the changes.
objConfig.Save();
Response.Write(string.Format("New value : {0}", objConfig.AppSettings.Settings["MyKey"].Value));

Transfer rows to column in C#

I was having requirement to change result of SQL in such a way that rows become columns, and columns become rows.

PersonName                Year2000               Year2001               Year2002               Year2003
------------------------- ---------------------- ---------------------- ------------------- ---------------
A 1230 4521 5435 5410
B 1330 4231 5435 1200
C 3230 1623 5435 3652
D 4230 2321 5435 1859


And need output as following


SalesYear       A                      B                      C                      D
--------------- ---------------------- ---------------------- ---------------------- ----------------------
Year2000 1230 1330 3230 4230
Year2001 4521 4231 1623 2321
Year2001 5435 5435 5435 5435
Year2001 5410 1200 3652 1859

Mr Jacob wrote post on this which is uses UNPIVOT operator which is in SQL Server 2005, you can find it here.

Here is the contributed code form Abidali Suthar [one of the developer in my team], which does the same thing but in C#.

I added extension method to DataTable, by calling SwapTable method on any DataTable instance; you can get the rows in columns and columns in rows. Lets look at extension method first.

public static class DatatTableExtension
{
/// <summary>
/// Extension method which transform row to column and column to row
/// </summary>
/// <param name="dt">Object on which we have to do operation</param>
/// <returns>Transformed DataTable object</returns>
public static DataTable SwapTable(this DataTable dt)
{
if (!(dt.Columns.Count > 0 && dt.Rows.Count > 0))
return dt;

DataTable dtNew = new DataTable();
dtNew.Columns.Add(dt.Columns[0].ColumnName);

// Creating columns for new DataTable.
// Adding column to new data table having name as first row of old data table
for (int i = 0; i <= dt.Rows.Count - 1; i++)
dtNew.Columns.Add(dt.Rows[i][0].ToString());

DataRow row;
// Swaping the values
for (int k = 1; k < dt.Columns.Count; k++)
{
row = dtNew.NewRow();
row[0] = dt.Columns[k].ToString();
for (int j = 1; j <= dt.Rows.Count; j++)
row[j] = dt.Rows[j - 1][k];

dtNew.Rows.Add(row);
}

return dtNew;
}
}

As we created extension method, its now easy to call it by creating object of DataTable. Lets see with example.

protected void Page_Load(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection("CONNECTION_STRING"))
{
con.Open();
string strSelect = "SELECT_STATEMENT";

SqlCommand cmd = new SqlCommand(strSelect, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
con.Close();

// Bind original datatable
dgBefore.DataSource = dt;
dgBefore.DataBind();

// Bind swapped datatable, calling extension method on datatable5
dgAfter.DataSource = dt.SwapTable();
dgAfter.DataBind();
}
}