Sql-server – CONVERT COLUMN INTO ROW

sql server

I have data like this .

    ID   COUNT
    A1   2
    B2   2
    C1   1

i want output like this

ID
A1
A1
B2
B2
C1

Best Answer

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]