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,

Solution 1:

You can use a recursive CTE:

;WITH CTE AS
(
    SELECT *
    FROM YourTable
    UNION ALL 
    SELECT id, pcs-1FROM CTE
    WHERE pcs-1 >= 1
)
SELECT *
FROM CTE
ORDERBY id, pcs
OPTION(MAXRECURSION 0)

Here is a demo for you to try.

Solution 2:

Here is my approach. Extremely easy with a Tally Table (A table that only has a column with a value 1 -> X). No need for recursion, and this will be much faster over larger tables.

Notice we are only making a Tally Table of 100 rows, feel free to expand that as large as you'd like. If you get too crazy, you might need another cross join in sys.sysobjects to accomdate. The real query is at the bottom, as you can see it's extremely easy.

SELECT TOP 100IDENTITY( INT,1,1 ) AS N 
 INTO #Tally
FROM    sys.sysobjects sc1 ,

        sys.sysobjects  sc2

CREATETABLE #Test
(
    Id char(3),
    pcs int
)

INSERTINTO #Test
SELECT'ABC', 3UNIONALLSELECT'DEF', 1UNIONALLSELECT'GHJ', 4SELECT #Test.Id, #Tally.N FROM #Tally
    JOIN #Test ON #Tally.N <= #Test.pcs
    ORDERBY #Test.Id

Solution 3:

SELECT
  id
 ,pcs_num
FROM MyTable
CROSS APPLY (
  SELECT TOP (pcs)
    ROW_NUMBER() OVER(ORDERBY (SELECT1)) pcs_num
  FROM master.dbo.spt_values
) t