组相关记录SQL Server 2012 [英] Group related records SQL Server 2012

问题描述

我在SQL Server中有一个表，它具有以下结构

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

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

ParentID列引用主键列

我使用此表存储Category的层次结构，没有限制深度。

根类别的父ID为零

子类别具有其所在类别的父ID。



无论记录如何存储在表格中

我想要实现的是按层次列出所有类别



喜欢： -

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

我会喜欢在ASP.net ListView控件中显示这个列表。



请建议一些解决方案，我应该如何编写SQL查询来实现这一点。

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.

推荐答案

我建议阅读MSDN文档：


ASP.NET中的分层数据绑定 [ ^ ]


演练：在TreeView控件中显示分层数据 [ ^ ]

I would suggest to read MSDN documentation:

Hierarchical Data Binding in ASP.NET[^]

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

这也许会有所帮助： ASP.NET ListView：显示分层数据 [ ^ ]

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

以下是我解决这个问题的方法： - < br $>

Here is how i Solved this :-

;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

这篇关于组相关记录SQL Server 2012的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！