# CONVERT COLUMN INTO ROW

I have data like this .

``````    ID   COUNT
A1   2
B2   2
C1   1
``````

i want output like this

``````ID
A1
A1
B2
B2
C1
``````

## 2 Answers

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
``````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
``````