Group related records SQL Server 2012

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

I have a table in SQL Server which has the following structure

SQL

Table Name Category
Column Name           Data Type
Ref_Cat_ID            bigint   Primary Key Identity Spec(1,1)
Cat_Title             varchar(MAX)
Cat_ShortName         varchar(MAX)
Cat_Desc              varchar(MAX)
ParentID              bigint
Created_By            varchar(MAX)
Created_DateTime      datetime
Last_Updated_By       varchar(MAX)
Last_Updated_DateTime datetime
IsDefault             bit
IsActive              bit
Flag                  bit

The primary key column is referenced by the ParentID column
I use this table to store hierarchy of Category with no restriction on depth.
Root Category has an parent id of zero
Child Category has an parent id of the category under which it lies.

No matter how the records are stored in the table
What i want to achieve is list all categories hierarchically

Like:-

Root category 1
 child category 1-1
 child category 1-2
   sub child category 1-2-1
Root Category 2 
 child category 2-1
 child category 2-2
   sub child category 2-2-1
     child category 2-2-1-1
     child category 2-2-1-2
     child category 2-2-1-3
     child category 2-2-1-4
       child category 2-2-1-4-1
       child category 2-2-1-4-2
       child category 2-2-1-4-3
       child category 2-2-1-4-4
     child category 2-2-1-5

I would like to show this list in the ASP.net ListView control.

Please suggest some solution as to how should i write a SQL query to achieve this.

Posted 4-Aug-15 18:32pm

Christopher Fernandes

Updated 4-Aug-15 19:41pm

Patrice T

v2

Add a Solution

Comments

PIEBALDconsult 5-Aug-15 0:46am

You will likely need a recursive Common Table Expression.

Christopher Fernandes 5-Aug-15 1:00am

Thanks for the suggestion.

2 solutions

Solution 1

I would suggest to read MSDN documentation:

Hierarchical Data Binding in ASP.NET[^]

Walkthrough: Displaying Hierarchical Data in a TreeView Control[^]

This might be helpful too: ASP.NET ListView: Displaying Hierarchical Data[^]

Posted 5-Aug-15 3:09am

Maciej Los

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christopher Fernandes · Accepted Answer · 2015-08-07T06:18:00

Here is how i Solved this :-

SQL

;WITH CategoryListCTE AS
		(
		SELECT  
		pc.*
		,CAST(ROW_NUMBER() OVER (ORDER BY pc.Ref_Category_ID) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN AS bc
		FROM WikiApp_Category pc
		WHERE pc.ParentID = 0
		UNION ALL
		SELECT  
		cc.*
		,CategoryListCTE.bc + '.' + CAST(ROW_NUMBER() OVER (PARTITION BY cc.ParentID ORDER BY cc.Ref_Category_ID) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN
		FROM WikiApp_Category cc
		JOIN CategoryListCTE
		ON cc.parentID = CategoryListCTE.Ref_Category_ID
		)
	SELECT  
	cl.Ref_Category_ID AS [Category_ID]
	,cl.Category_Title AS [Category_Title]
	,cl.Category_ShortName AS [Category_Short_Name]
	,cl.Category_Desc AS [Category_Desc]
	,cl.ParentID AS [Parent_Category]
	,ISNULL(dbo.WikiApp_GetCategoryName(cl.ParentID),'N/A') AS [Parent_Name]
	,CASE cl.AllowDelete
		WHEN 1 THEN 'Yes'
		WHEN 0 THEN 'No'
	END AS [AllowDelete]
	,cl.IsDefault AS [Default]
	,cl.IsActive AS [Active]
	,CASE cl.IsDefault
		WHEN 1 THEN 'Yes'
		WHEN 0 THEN 'No'
	END AS [IsDefault]
	,CASE cl.IsActive
		WHEN 1 THEN 'Yes'
		WHEN 0 THEN 'No'
	END AS [IsActive]
	,cl.Created_By 
	,CONVERT(VARCHAR(20),cl.Created_DateTime,113) AS [Created_DateTime]
	,cl.Last_Updated_By
	,CONVERT(VARCHAR(20),cl.Last_Updated_DateTime,113) AS [Updated_DateTime]
	,cl.Flag
	FROM CategoryListCTE cl
	WHERE cl.Flag = 1