How can I expand a string into multiple rowset results?

Question

I have a table in which I try to tear each row into one or more rows based on the value of the second column. 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 write this as sproc in SQL Server 2008. My best solution is to use a cursor and add [pcs] the number of rows in the temp table for each row in the table. It seems like there should be a simpler solution than this, which I am missing. Thanks.

Answer 1

You can use 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.

Answer 2

Here is my approach. Very simple with a Tally table (a table that has only a column with a value of 1 → X). There is no need for recursion, and it will be much faster in large tables.

Please note that we only make a Tally table of 100 rows, feel free to expand it as much as you want. If you are too crazy, you may need another cross join in sys.sysobjects to host. The actual request is at the bottom, as you can see it very easily.

 SELECT TOP 100 IDENTITY( INT,1,1 ) AS N INTO #Tally FROM sys.sysobjects sc1 , sys.sysobjects sc2 CREATE TABLE #Test ( Id char(3), pcs int ) INSERT INTO #Test SELECT 'ABC', 3 UNION ALL SELECT 'DEF', 1 UNION ALL SELECT 'GHJ', 4 SELECT #Test.Id, #Tally.N FROM #Tally JOIN #Test ON #Tally.N <= #Test.pcs ORDER BY #Test.Id 

Answer 3

 SELECT id ,pcs_num FROM MyTable CROSS APPLY ( SELECT TOP (pcs) ROW_NUMBER() OVER(ORDER BY (SELECT 1)) pcs_num FROM master.dbo.spt_values ) t