How can I expand out a row into multiple row result set?

sql, sql-server, sql-server-2008

Solution

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)

Here is a demo for you to try.

Problem

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 ``` 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.

Original source