如何将一行扩展为多行结果集? [英] How can I expand out a row into multiple row result set?

问题描述

我有一个表格，我试图根据第二列的值将每一行分成一行或多行.像这样:

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)

这里有一个演示供您尝试.

这篇关于如何将一行扩展为多行结果集?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！