CONVERT COLUMN INTO ROW

Question

I have data like this .

    ID   COUNT
    A1   2
    B2   2
    C1   1

i want output like this

ID
A1
A1
B2
B2
C1

Answer 1

WITH 
cte1 AS ( SELECT MAX([count]) maxcount
          FROM datatable
        ), 
cte2 AS ( SELECT 1 num
          UNION ALL
          SELECT num+1 FROM cte1, cte2 WHERE num < maxcount
            )
SELECT id
FROM datatable, cte2
WHERE [count] >= num
ORDER BY id

Answer 2

The approach is:

1. Determine the highest value for [COUNT] (if you know this can't be more than 10 or 20 or <some number>, just hard-code it as <that> + 1 or <that> * 2).

2. Generate a sequence of numbers, and join to it (producing for any given ID a row for each integer up to and including [COUNT]).

   DECLARE @n int = (SELECT MAX([COUNT]) FROM dbo.table);
   
   ;WITH n(n) AS 
   (
     SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n < @n
   )
   SELECT t.ID FROM dbo.table AS t
     INNER JOIN n ON n.n <= t.[COUNT]
     ORDER BY t.ID;
   

   If [COUNT] can be > 100, you'll need to add OPTION (MAXRECURSION n), because recursive CTEs are by default limited to 100.

3. If you have a numbers table (and everyone should, IMHO) that starts at 1 and includes the highest possible value for [COUNT], it's slightly simpler, since you don't need to pre-determine the end of the range:

   SELECT t.ID FROM dbo.table AS t
     INNER JOIN dbo.Numbers AS n 
       ON n.NumberColumn <= t.[COUNT]
     ORDER BY t.ID;
   

   If your numbers table starts at 0, you'll need an additional clause to prevent 0 from adding an extra row for every ID:

       ON n.NumberColumn > 0 AND n.NumberColumn <= t.[COUNT]