Row_Number() function in SQL Server 2005

 

As we all know, SQL Server 2005 has new features when compared to SQL Server 2000. One of the features that we are going to discuss in this article is the Row_Number() function.
Traditionally we used TOP keyword, temporary tables and co-related sub-queries to generate calculated row numbers in a query. Now SQL Server 2005 provides a function, which replaces all of the additional resources we used to generate row numbers. It is not only used to generate row number for each row but also used for other purposes as well.

Let us assume that we have the following database [Test] and the following table [Orders] in the database. You can use the below query to create the database, table and all the corresponding rows.

-- Create Table Name Products
CREATE TABLE Products(
Prod_NO VARCHAR(10) NOT NULL primary Key,
Prod_Name VARCHAR(100),
Prod_Amt INT
)
GO

-- Insert into Products Tables with some values
INSERT INTO Products VALUES ('P000000001', 'Chemicals 1', 10000)
INSERT INTO Products VALUES ('P000000002', 'Chemicals 2', 13000)
INSERT INTO Products VALUES ('P000000003', 'Chemicals 3', 8000)
INSERT INTO Products VALUES ('P000000004', 'Chemicals 4', 5000)
INSERT INTO Products VALUES ('P000000005', 'Chemicals 5', 21000)
GO

-- Verify Values
SELECT * FROM Products

ROW_NUMBER is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause, beginning with 1.

1) Generating Row Number for Each Row after ordering by Prod_Amt.

Following Query will return all result from Products table ordered by with the Amount and assigning each row a Row_Number beginning with 1.

SELECT ROW_NUMBER() OVER(Order by Prod_Amt) as Row_ID, * from Products

Result:

Row_ID	Prod_NO	Prod_Name	Prod_Amt
1	P000000004	Chemicals 4	5000
2	P000000003	Chemicals 3	8000
3	P000000001	Chemicals 1	10000
4	P000000002	Chemicals 2	13000
5	P000000005	Chemicals 4	21000

2) Implement top-N, bottom-N, and inner-N reporting Using Sub Query

By nesting a sub query using ROW_NUMBER inside a query that retrieves the ROW_NUMBER values for a specified range, you can find a precise subset of rows from the results of the inner query. This use of the function lets you implement top-N, bottom-N, and inner-N reporting. For consistent results, the query must ensure a deterministic sort order.
E.g. we want to retrieve Top 2 Product Range which is not more than 10000 in price.

SELECT * FROM (
SELECT ROW_NUMBER() OVER(Order by Prod_Amt DESC) as Row_ID, * from Products
WHERE Prod_Amt <=10000) TopProduct WHERE Row_ID <=2

Result:

Row_ID	Prod_NO	Prod_Name	Prod_Amt
1	P000000001	Chemicals 1	10000
2	P000000003	Chemicals 3	8000

3) Finding Nth maximum Amount of Product
By nesting a sub query using ROW_NUMBER inside a query that retrieves the ROW_NUMBER values for distinct values for a Product Amount, you can find Nth Maximum Product Amount from the results of the inner query

-- add two products with same amount
INSERT INTO Products VALUES ('P000000006', 'Chemicals 6', 13000)
INSERT INTO Products VALUES ('P000000007', 'Chemicals 7', 8000)

SELECT Prod_Amt FROM (
SELECT ROW_NUMBER() OVER(Order by Prod_Amt) as Row_ID, Prod_Amt
FROM (Select Distinct(Prod_Amt) from Products) DistinctAmts) Nth_Amount
WHERE Row_ID = 2 –- This will return you 2nd maximum product price

Result:

Prod_Amt

8000

4) The same Row_Number function can be used to delete duplicate entries. (that is exercise to you)

Using Common Table Expression

Many projects store the hierarchical data into database. This hierarchical data is used to generate the tree view on UI like folder structure in the explorer, location hierarchy and company hierarchy in account software.

Generally data is stored in the database in a one table. Let’s take an example so that we can understand better. Let’s say we need to create the location hierarchy in form of tree view on screen. It will look like this

Asia (1)
- India (3)
- Mumbai (7)
- Delhi (8)
- Calcutta (9)
- Bangalore (10)
- Japan (4)
- Tokyo (11)
- Nagasaki (12)
- Hiroshima (13)
Europe (2)
- England (5)
- London (14)
- Manchester (15)
- France (6)
- Paris (16)

Here we assumed that data in brackets are their Locationid. So our database table will look like below and u can also create in u r sample database.

CREATE TABLE Locations
(
LocationID int primary key not null,
ParentLocationID int null,
LocationName varchar(50) not null
)

Insert the values …..

INSERT Into Locations Values (1, null, 'Asia')
INSERT Into Locations Values (2, null, 'Europe')
INSERT Into Locations Values (3, 1, 'India')
INSERT Into Locations Values (4, 1, 'Japan')
INSERT Into Locations Values (5, 2, 'England')
INSERT Into Locations Values (6, 2, 'France')
INSERT Into Locations Values (7, 3, 'Mumbai')
INSERT Into Locations Values (8, 3, 'Delhi')
INSERT Into Locations Values (9, 3, 'Calcutta')
INSERT Into Locations Values (10, 3, 'Bangalore')
INSERT Into Locations Values (11, 4, 'Tokyo')
INSERT Into Locations Values (12, 4, 'Nagasaki')
INSERT Into Locations Values (13, 4, 'Hiroshima')
INSERT Into Locations Values (14, 5, 'London')
INSERT Into Locations Values (15, 5, 'Manchester')
INSERT Into Locations Values (16, 6, 'Paris')

To get the above mentioned o/p we can use Recursive Stored Procedure. But if u write recursive stored procedure it supports only 32 levels. And u might land up with the following error….

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Hence there is another way to get rid of this explained below…

CREATE PROCEDURE GetLocationTree_OneQuery
@LocationID int = 1
AS
BEGIN
WITH LocationTree(LocationID, ParentLocationID, LocationName, Levels)
AS
(
-- anchor member
SELECT LocationID, ParentLocationID, LocationName, 1
FROM Locations
WHERE LocationId = @LocationID

UNION ALL

-- recursive member
SELECT
L.LocationID, L.ParentLocationID, L.LocationName, LocationTree.Levels + 1
FROM Locations L
INNER JOIN LocationTree
ON LocationTree.LocationID = L.ParentLocationID
)

SELECT * From LocationTree
END

If look at carefully this stored procdedure has a 2 queries one with createting a temporay table for use and fireing recursive trigger internally and the last second one just gives the o/p table.

“With” statement creates the temp table “LocationTree” for use with the columns that we can define. Using this table we can fire the recursive query with the original table.
It has also limited of recursion up to 100 levels………. But u in real world u might not required too many levels




Recursive Stored Procedure

CREATE PROCEDURE GetLocations
@LocationID int = 1
AS
BEGIN
Create Table #NestedTree(
LocationID int,
ParentLocationID int,
LocationName varchar(50),
LocationLevel int
)

INSERT into #NestedTree SELECT LocationID, ParentLocationID,
LocationName, 1
FROM Locations where LocationID = @LocationID

EXEC GetLocationTree @LocationID, 1

SELECT
LocationID,
ParentLocationID,
LocationName,
LocationLevel
FROM #NestedTree

DROP TABLE #NestedTree

END

GO

CREATE PROCEDURE GetLocationTree
@LocationID int = 1,
@Level int = 0
AS
BEGIN

SET NOCOUNT ON
DECLARE @NextLevel int, @PLocationID int, @LocationName VARCHAR(50)

-- Build a cursor to loop through all the kids of this post
DECLARE c1 CURSOR LOCAL
FOR SELECT LocationID, LocationName FROM Locations
WHERE ParentLocationID = @LocationID

SET @PLocationID = @LocationID

SET @Level = @Level + 1

OPEN c1

FETCH NEXT FROM c1 INTO @LocationID, @LocationName

WHILE @@FETCH_STATUS = 0

BEGIN
-- Put this record in the temp table
INSERT #NestedTree (LocationID,
ParentLocationID,
LocationName,
LocationLevel)
VALUES (@LocationID, @PLocationID, @LocationName, @Level)

-- Process all the children for this post
EXEC GetLocationTree @LocationID, @Level

-- And get the next record at this level
FETCH NEXT FROM c1 INTO @LocationID, @LocationName
END

CLOSE c1
DEALLOCATE c1
SET NOCOUNT OFF
END

/////// ************************************* ///////

Run the Stored Procedure query in the Query Analyzer

GetLocations 1
This will give you Asia and the location below that with the levels also if the levels are here limited to three but if you think of unlimited levels this recursive procedure will fail since it only supports up to 32 levels and u might land up with following error.

Msg 217, Level 16, State 1, Procedure GetLocationTree
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).


That means u cannot call stored procedure under the stored procedure after 32 levels. So what is other solution to get rid of this? Definitely Microsoft has answer to this also.

Use of PIVOT & UNPIVOT in SQL Server 2005

Most of the time when you came across queries which need to convert the columns into rows and rows into columns. it was not possible with the SQL server 2000 but now you can use the PIVOT and UNPIVOT relational operators to manipulate a table-valued expression into another table in SQL SERVER 2005. A common scenario where PIVOT can be useful is when you want to generate cross-tabulation reports to summarize data.

here i have given some examples that to achieve this task and to understand better those who have not worked on the PIVOT & UNPIVOT.

PIVOT function :- Here i have taken an example of project and related tasks and no of persons assigned to each task of project.

1. Create a table tblResource

CREATE TABLE [dbo].[tblResource](
[Project] [varchar](10) NULL,
[Task] [varchar](10) NULL,
[Persons] [int] NULL
) ON [PRIMARY]

2. Insert data into it. this data will contain how many person working for given task on that project.

INSERT INTO [tblResource] VALUES ('Proj1', 'Task1', 3)
INSERT INTO [tblResource] VALUES ('Proj1', 'Task2', 2)
INSERT INTO [tblResource] VALUES ('Proj1', 'Task3', 4)
INSERT INTO [tblResource] VALUES ('Proj1', 'Task4', 1)
INSERT INTO [tblResource] VALUES ('Proj1', 'Task5', 3)
INSERT INTO [tblResource] VALUES ('Proj2', 'Task1', 4)
INSERT INTO [tblResource] VALUES ('Proj2', 'Task2', 2)
INSERT INTO [tblResource] VALUES ('Proj2', 'Task3', 3)
INSERT INTO [tblResource] VALUES ('Proj2', 'Task4', 5)
INSERT INTO [tblResource] VALUES ('Proj2', 'Task5', 7)

3. we want o/p as shown below.

ProjectName Task1 Task2 Task3 Task4 Task5
Proj1 3 2 4 1 3
Proj2 4 2 3 5 7

to get above o/p we can write query ---------------

SELECT Project AS ProjectName, Task1, Task2 ,Task3 , Task4, Task5
FROM
(SELECT Project, Task, Persons
FROM tblResource) Table1
PIVOT
(SUM(Persons) FOR Task IN
(Task1, Task2 ,Task3 , Task4, Task5)
)AS Table2

This means that the unique values returned by the Task column themselves become fields in the final result set. As a result, there is a column for each Task specified in the pivot clause — in this case they are Task1, Task2, Task3, Task4, Task5. The Persons column serves as the value column, against which the columns returned in the final output, called the grouping columns, are grouped i.e(Table1) . In this case, the grouping columns are aggregated by the SUM function. Table1 should return the unique values.

UNPIVOT function :- UNPIVOT performs almost the reverse operation of PIVOT, by rotating columns into rows.before doing any thing create a another table named [tblResource1] where as per the stucture of the above output.

1. Create Table tblResource1

CREATE TABLE [dbo].[tblResource1](
[ProjectName] [varchar](10) NULL,
[Task1] [int] NULL,
[Task2] [int] NULL,
[Task3] [int] NULL,
[Task4] [int] NULL,
[Task5] [int] NULL
) ON [PRIMARY]

2. insert data into it

INSERT INTO [tblResource1] VALUES ('Proj1',3,2,4,1,3)
INSERT INTO [tblResource1] VALUES ('Proj2',4,2,3,5,7)

3. we want a o/p as shown below.

Project TASK Persons
-------- --------- ---------
Proj1 Task1 3
Proj1 Task2 2
Proj1 Task3 4
Proj1 Task4 1
Proj1 Task5 3
Proj2 Task1 4
Proj2 Task2 2
Proj2 Task3 3
Proj2 Task4 5
Proj2 Task5 7

to get above o/p we can write query ---------------

SELECT ProjectName AS Project, CAST(Task AS VARCHAR) TASK, Persons
FROM
(SELECT ProjectName, Task1, Task2, Task3, Task4, Task5
FROM tblResource1) Table1
UNPIVOT
(Persons FOR Task IN
(Task1, Task2, Task3, Task4, Task5)
)AS Table2

This means that you must identify two additional columns. The column that will contain the column values you are rotating (Task1,Task2,...) will be called Task, and the column that will hold the values that currently reside under the columns being rotated will be called Persons. These columns correspond to the pivot_column and value_column, respectively, in the Transact-SQL definition.

Note that UNPIVOT is not the exact reverse of PIVOT. PIVOT performs an aggregation and hence merges possible multiple rows into a single row in the output.

Accessing .Net Admin site interface without VS.Net

.Net Admin site interface be made available to the users without Visual Studio, Follow the following Steps.

1. Create a virtual directory that points to the web admin files.

VirtualDirectory: ASP.NetWebAdminFiles
MappedTo: C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\ASP.NETWebAdminFiles

2. Modify the properties of the virtual directory so that it is running under ASP.NET 2.0. (Properties > ASP.NET)

NOTE: if you are running 1.1 and 2.0 applications on the same server or site, you may have to set up a separate application pool for the 2.0 sites. If you get the notice, "Application Unavailable" then that is why.

3. While you are there, remove anonymous access to that virtual directory.

4. After that, you will be able to connect to the web admin tools using the following url syntax

http://localhost/ASP.NETWebAdminFiles/default.aspx?applicationPhysicalPath=XXX&applicationUrl=/YYY

Where,
XXX: - Application Physical Path
YYY: - Application URL


In Framework case, it is:

http://localhost/asp.netwebadminfiles/default.aspx?applicationPhysicalPath=C:\Plan\Construction\Framework\SBWebSite\&applicationUrl=/SBWebSite
OR http://mdc0514c/asp.netwebadminfiles/default.aspx?applicationPhysicalPath=C:\Plan\Construction\Framework\SBWebSite\&applicationUrl=/SBWebSite (for remotely)

Note:

· Here we donot have to specify the port number since we hosted Admin interface on IIS.
· Only do this if you are careful and for the right purposes, since you are exposing some administration capability.

· Although it isn't recommended, if you want to access web admin tool from a different/remote computer, then open the WebAdminPage.cs file from the C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\ASP.NETWebAdminFiles\App_Code directory and comment the following code block:if (!application.Context.Request.IsLocal)
{
SecurityException securityException = new SecurityException((string)HttpContext.GetGlobalResourceObject("GlobalResources", "WebAdmin_ConfigurationIsLocalOnly"));

WebAdminPage.SetCurrentException(application.Context, securityException);

application.Server.Transfer("~/error.aspx");
}

Editing Web.config file from code behind

Hi friends,
Some times you might have thought that it would make things convenient to write back to your .NET application's config file. The framework provides simple methods for reading from the config file, but gives you nothing for writing values back to the config file. It is easy enough to write values back to the file. It's only XML file.

I am using framework 2.0. Let’s we will make one application in ASP.NET. Our application contains the “web.config” file and our application aim is to modify the config file. For that we will add one key in “appsetting” section of the config file as shown below. We will allow application to change the value of the key “connectionstring” from “sandy” to “testapplication”.

Change/ add in web.config:

<appSettings>
<add key="ConnectionString" value="sandy" />
</appSettings>

To write values back, you just need to open the config file as an XmlDocument and write away. No big deal. You can add name/value pairs, remove elements, and modify them or whatever. Copy the code and paste it in the codebehind of the page. This code will allow you to change “appSettings” section of the config file based on the key name.

For this you need to make the aspx page for the front end. With the one text box demanding for the value to change and submit button.

Code is given below:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Xml;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
string ConnectionString;
ConnectionString = txtvalue.Text.ToString(); // or else you can take the parameters from the form
//and create your own connectionstring
// like string ConnectionString = @"Server=local;Database=" + txtDatabaseName.Text + ";Trusted_Connection=true";

// set the web.config path to read / change
string path = Server.MapPath("Web.config"); // root web.config

// we will search for the appSetting entity
XmlDocument xDoc = new XmlDocument();

xDoc.Load(path); // load the web.config

XmlNodeList nodeList = xDoc.GetElementsByTagName("appSettings");

XmlNodeList nodeAppSettings;

XmlNode node;

// search the key
nodeAppSettings = nodeList[0].ChildNodes; // get all the nodes present under
// appSetting

node = nodeList[0].ChildNodes[0]; // take the First node i.e. [add] node with [key name] ConnectionString
// [Add] - Node name
// [Key] - Attribute Name
// [Value] - Attribute Name

//int nnodes;
//for (nnodes = 0; nnodes <= nodeList.Count - 1; nnodes++)
//{
// if(nodeList[0].ChildNodes.
// nodeAppSettings = nodeList[0].ChildNodes;
//}

XmlAttributeCollection xmlAttCollection = node.Attributes;

// you can even change the key name
//xmlAttCollection[0].InnerXml = txtKey.Text; // for key attribute
xmlAttCollection[1].InnerXml = ConnectionString.ToString(); // for value attribute

// Writting Web.config file
xDoc.Save(path); // saves the web.config file
}
}



Note: it is not good to write back to the config file. The framework does not include this ability for a reason.

Membership Provider Control in ASP.Net

Hi Friends,

Here I am demonstrating how use membership provider control for login purposes. Microsoft has provided the login control that enables the developer to create the functionality of authorizing the user quickly. When I was developing this for site I encounter some problem. That also I am going to discuss over here.

First when you start to implement the membership provider control into your site. You need to decide which pages or which folder you want to restrict the user unless he/she is authorized. After deciding this, create your pages or pages in folder.
Here I have created a page PriceListing.aspx and I want to restrict the anonymous user to access it. User either has to sign in or Sign Up to view the page. I have also created the pages default.aspx, MemberLogin.aspx and CreateUser.aspx.

Default.aspx is the default page for site. If user is already logged in then the user status will be logout else login. This was related to UI. Not come to the configuration membership provider control to use the login facility provided by the Microsoft in VS.NET 2005.

In web.config on has to do the following changes.

1. Create the connection string which will point to the database.
<connectionStrings>
<add name="MPConnectionString" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=DataDirectory\ASPNETDB.MDF; Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />

<!--<add name="MPConnectionString" connectionString="Data Source=.\SQLExpress;Persist Security Info=True;Integrated Security=SSPI;Initial Catalog=TestDatabase"/>
-->
</connectionStrings>

Here you can see two types of connection string

         1. In first connection string
AttachDbFilename=DataDirectory\ASPNETDB.MDF
This line automatically creates the database names as ASPNetDB.mdf in to your site/App_Data folder. It also automatically creates the tables into it which are required for membership controls.
         2. in second connection string you are specifying your own database for provider, hence it does not have the membership provider tables in it. You can create that using aspnet_regsql.exe utility. (please do not forget to give rights for membership provider to the public role )


2. Write the following statements in <system.web>.

<identity impersonate="true"/>
<membership defaultProvider="MemberShipSandeepTest">
<providers>
<add connectionStringName="MPConnectionString" name="MemberShipSandeepTest" type="System.Web.Security.SqlMembershipProvider" />
</providers>
</membership>

Here we have create the membership provider named as MemberShipSandeepTest. Specified connection string as MPConnectionString. Specified type as System.Web.Security.SqlMembershipProvider.

Automatically Microsoft provides the default provider we have to change the default provider to our newly created provider. This can be done very easily by specifying one of the attribute of the membership as defaultProvider="MemberShipSandeepTest" our provider name.

3. Next step is to define the authentication and authorization settings.

<authentication mode="Forms">
<forms cookieless="UseCookies" name="AuthCookie" defaultUrl="Default.aspx" loginUrl="MemberLogin.aspx" protection="All" timeout="10">
</forms>
</authentication>

Make the authentication mode as forms
There are total 4 types of authentication Windows, Forms, Passport, None. We are going to use Forms Authentication.
If Authentication mode is Forms we have to specify 2 things mainly:

· Cookieless = specify the “UseCookies” since we required the cookies for authorization of users.
· defaultUrl = user is redirected to this URL if he/she is directly want to login. And no return URL is present.
· loginUrl = if user is not authenticated and not authorized to access the page visited then system automatically redirects the user to this page.

4. Next step allow all user to access all the pages in site by specifying

<authorization>
<allow users="*"/>
</authorization>

Where * denotes all users.

5. Next step is to restrict the user to some pages or the folders.
To restrict the user before logging in to site can be possible from the web.config. you just have to write few lines in web.config as specified below.

In our example we want to restrict the unauthorized user to access the PriceListing.aspx
Write below line outside <system.web>.

<location path="PriceListing.aspx">
<system.web>
<authorization>
<deny users="?"/>
</authorization>
</system.web>
</location>

? Denotes the anonymous user.
In path you can also specify the folder path. Specifying the folder path allows you to impose the authorization for all pages in side the folder.



You can download the example as per the above discussion…..
http://aspspider.net/sandeeppawar/ArticlesItems/AD_11_LoginControl.zip
(You can customize the directions like continue button when user sign up. Log out click.)

SQL - Recursive Cursor with tree structure data

This article aims for the same result but keeps your table as simple as possible. It also allows us to do some pretty neat things with recursive stored procedures. My examples try to cover both posts and the hierarchical numbering scheme from the question. It should work for any generic "expanding hierarchy" problem.

We'll start with a table that looks like this: *

=====================================================================

CREATE TABLE [Posts] (
[PostID] [int] IDENTITY (1, 1) NOT NULL ,
[Subject] [char] (50) NULL ,
[ParentID] [int] NULL ,
[PostSortKey] [datetime] NOT NULL
)
GO

ALTER TABLE [Posts] WITH NOCHECK
ADD CONSTRAINT [DF_Posts_SortKey] DEFAULT (getdate()) FOR [PostSortKey]
GO *

=====================================================================


The PostID column is our primary key for this table. Subject is the subject of the post. For simplicity sake I removed all other post related fields that were not absolutely necessary such as author and the body of the post. ParentID is the PostID of the post for the parent. If this post is a top level post, then this field will be zero. PostSortKey is the sort order for the posts. This will usually be a datetimefield. In my example here, I used a default of GETDATE() to populate the field. In the specific example of this question it could be whatever the user wanted.

My example will use a dataset that looks like this: *

PostID Subject ParentID PostSortKey
1 First Post 0 2000-12-06 20:54:29.407
2 Second Post 0 2000-12-06 20:54:29.407
3 Child of First Post 1 2000-12-06 20:54:29.407
4 Child of Second Post 2 2000-12-06 20:54:29.407
5 Child of First Child 3 2000-12-06 20:54:29.407
6 Another FP Child 1 2000-12-06 21:04:49.217
7 Smallest Kid 5 2000-12-06 21:18:49.203
8 Another Munchkin 3 2000-12-06 21:28:22.040
*

Populating a table like this should be very easy. You just need to insert a record with its parent ID.

This example is composed of two pieces of code. The first is a SQL Script to create a temporary table and call the stored procedure. It looks like this: *

===========================================================

Create Table #NestedPosts (
SortID int IDENTITY (1,1), PostID int, PostKey varchar(200), PostLevel int
)*
*

exec getchildren 0, 1, ''

*

SELECT
SortID, P.PostID, ParentID,
PostLevel,
PostKey = LEFT(PostKey, 10),
PostSortKey = convert(varchar(19), PostSortKey, 120),
Subject = LEFT( SPACE( (PostLevel-1) * 2 ) + Subject , 40)
FROM
#NestedPosts N JOIN Posts P
ON N.PostID = P.PostID
Order by
SortID

*

DROP TABLE #NestedPosts

===========================================================

The temporary table is populated by the stored procedure. The final SELECT prints out the results. Next is the stored procedure. It looks like this: *

===========================================================

CREATE PROC GetChildren
(
@ParentID int, @PostLevel int, @ParentPostKey varchar(200)
)
AS

BEGIN

SET NOCOUNT ON
DECLARE @NextLevel int, @Counter int, @PostKey varchar(200)
SET @Counter = 1

-- Build a cursor to loop through all the kids of this post

DECLARE c1 CURSOR LOCAL
FOR SELECT PostID FROM Posts
WHERE ParentID = @ParentID Order by PostSortKey ASC

OPEN c1

FETCH NEXT FROM c1 INTO @ParentID

WHILE @@FETCH_STATUS = 0

BEGIN
-- Build a key up as we go
IF @PostLevel = 1
SET @PostKey = convert(varchar, @Counter)
ELSE
SET @PostKey = @ParentPostKey + '.' + convert(varchar, @Counter)

-- Put this record in the temp table
INSERT #NestedPosts (PostID, PostKey, PostLevel)
VALUES (@ParentID, @PostKey, @PostLevel)

SET @NextLevel = @PostLevel + 1

-- Process all the children for this post
EXEC GetChildren @ParentID, @NextLevel, @PostKey

SET @Counter = @Counter + 1

-- And get the next record at this level

FETCH NEXT FROM c1 INTO @ParentID
END

CLOSE c1
DEALLOCATE c1
SET NOCOUNT OFF
END

===========================================================

*
This stored procedure runs through all the child posts for a given parent post and puts them into the temp table. It also builds up the hierarchical numbering scheme that was asked about in the question. The really tricky part is that it calls itself in order to handle any child records of the current record it's processing. You can have up to 32 levels of nesting in your stored procedure.

That means this little sample is limited to 32 levels of posts. I also could have used a variable called @@NESTLEVEL which SQL Server uses to track how far down a recursion tree you are. I built and manually maintained a variable called @PostLevel for the task.

The output from the SELECT statement above looks like this: *

SortID PostID ParentID PostLevel PostKey PostSortKey Subject
1 1 0 1 1 2000-12-06 20:54:29 First Post
2 3 1 2 1.1 2000-12-06 20:54:29 Child of First Post
3 5 3 3 1.1.1 2000-12-06 20:54:29 Child of First Child
4 7 5 4 1.1.1.1 2000-12-06 21:18:49 Smallest Kid
5 8 3 3 1.1.2 2000-12-06 21:28:22 Another Munchkin
6 6 1 2 1.2 2000-12-06 21:04:49 Another FP Child
7 2 0 1 2 2000-12-06 20:54:29 Second Post
8 4 2 2 2.1 2000-12-06 20:54:29 Child of 2nd Post


* That's about all there is to this. Try it and let me know what you think.