-1

I have data like this .

    ID   COUNT
    A1   2
    B2   2
    C1   1

i want output like this

ID
A1
A1
B2
B2
C1
1

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]
    
  • YES. thanks alot! – user172047 May 31 at 11:34
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.