组相关记录SQL Server 2012

本文介绍了组相关记录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 ListView控件中显示这个列表。 请建议一些解决方案，我应该如何编写SQL查询来实现这一点。

I would like to show this list in the ASP 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[^]

以下是我解决这个问题的方法： - 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