本文介绍了如何将一行扩展为多行结果集?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个表格,我试图根据第二列的值将每一行分成一行或多行.像这样:
I have a table that I'm trying to break out each row into one or more rows based on the second column value. Like this:
table (id, pcs):
ABC 3
DEF 1
GHJ 4
query result (id, pcs_num):
ABC 1
ABC 2
ABC 3
DEF 1
GHJ 1
GHJ 2
GHJ 3
GHJ 4
我将此作为 SQL Server 2008 中的 sproc 编写.我最好的解决方案是使用游标并将 [pcs] 行数添加到临时表中的每一行.似乎必须有一个比我缺少的更简单的解决方案.谢谢.
I'm writing this as a sproc in SQL server 2008. My best solution is to use a cursor and add [pcs] number of rows to a temp table for each row in the table. Is seems like there must be a simpler solution than this that I am missing. Thanks.
推荐答案
您可以使用递归 CTE:
You can use a recursive CTE:
;WITH CTE AS
(
SELECT *
FROM YourTable
UNION ALL
SELECT id, pcs-1
FROM CTE
WHERE pcs-1 >= 1
)
SELECT *
FROM CTE
ORDER BY id, pcs
OPTION(MAXRECURSION 0)
这里有一个演示供您尝试.
这篇关于如何将一行扩展为多行结果集?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!