问题描述

我有一个包含 2 列的表格:ID, ParentID

I have a table with 2 columns: ID, ParentID

我想创建一个新表/视图，每一行都包含所有级别的所有孩子到某个父母...

i want to make a new table/view that each row includes all the children in all levels to a certain parents...

例如:

ParentID  Level_1  Level_2  Level_3 ... Level_n

这意味着 parentID 是 Level_1 的父级，它是 level_3 的父级 2 的父级，依此类推...

it means that parentID is the parent of Level_1 which is the Parent of level 2 which is the parent of level_3 and so on...

我需要做没有递归所以没有机会堆栈溢出...

i need to do it with no recursion so no chance to stack overflow...

在 T-SQL 中有一个名为 @@rowcount 的函数，它返回我在最后一次选择中得到的行，所以也许我可以在 while 循环或其他东西中使用它...

in T-SQL there's a function called @@rowcount which returns the rows i got in the last select so maybe i can use it inside a while loop or something...

问题是我怎么知道一开始要构造多少列?知道如何参加这个挑战吗?

the question is how can i know how many columns to construct at the beginning ? any idea how to access this challenge ?

推荐答案

你想要得到的是一个数据透视表，但在我看来，这个任务不属于数据库.我宁愿得到一个包含行的结果集并从外部"旋转它们.

What you're trying to get is a pivot table, but, in my opinion, this task doesn't belong to the database. I'd rather get a result set with rows and pivot them "externally".

为了实现我所描述的，在 SQL Server 2005+ 中，您可以使用公共表表达式 (在这里你可以找到一个例子，而 SQL Server 2000 需要一个稍微不同的方法.

To achieve what I described, in SQL Server 2005+ you can use a Common Table Expression (here you can find an example, while SQL Server 2000 requires a slightly different approach.

注意:虽然你写了没有递归所以没有机会堆栈溢出"，你仍然需要保护自己免受无限循环的影响.我个人使用递归并为其建立最大深度"；当递归尝试比限制更深"时，我停止它并返回错误.

Note: although you wrote "no recursion so no chance to stack overflow", you'll still have to proect yourself from infinite loops. I personally use recursion and establish a maximum "depth" for it; when the recursion attempts to go "deeper" than the limit, I stop it and return an error.