# 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


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]