List same row twice if the count is more than 1 in another column

Question

i came across this requirement to list the row twice in the output if the corresponding column value is more than 1.

Calls       Abandoned
Microsoft   1
Apple       1
IBM         2
CISCO       3

Output Needed:
Calls       Abandoned
Microsoft   1
Apple       1
IBM         1
IBM         1
CISCO       1
CISCO       1
CISCO       1

I tried to use ranking function in sql, doesn't seems to work. Any solutions guys?

Possible duplicate of https://stackoverflow.com/questions/33327837/repeat-rows-n-times-according-to-column-value — RToyo, Oct 04 '17 at 21:05
Possible duplicate of [Repeat Rows N Times According to Column Value](https://stackoverflow.com/questions/33327837/repeat-rows-n-times-according-to-column-value) — Radim Bača, Oct 04 '17 at 21:07
`CISCO` doesn't appear twice, but thrice. What is your "requirement"? May we see the code you tried? — HABO, Oct 04 '17 at 22:12

Gordon Linoff · Answer 1 · 2017-10-04T23:19:59.627

4

One method uses a numbers table or CTE. Or, if you have just a handful of rows for a given call, then a recursive CTE is easy enough:

with cte as (
      select call, abandoned
      from t
      union all
      select call, abandoned - 1
      from cte
      where abandoned > 1
    )
select call, 1 as abandoned
from cte;

This works fine up to 100 rows per call. After that, you would need to set MAXRECURSION to a higher value.

EDIT:

Here is a rextester.

edited Oct 04 '17 at 23:19

answered Oct 04 '17 at 21:05

Gordon Linoff

1,242,037
58
646
786

Thanks Gordon. This worked great but did not give me full records. I should get total 102 records but this technique gave me only 97 records. Do you know why? – axlrose89 Oct 04 '17 at 21:59
@axlrose89 . . . This should return one row for every call. Is abandoned an integer? Does it have unusual values? – Gordon Linoff Oct 04 '17 at 23:20

RoMEoMusTDiE · Answer 2 · 2017-10-04T21:26:55.060

Another way is using master..spt_values

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b5f13a70-6ff5-4704-83d6-e1cd68f172b5/select-the-numbers-between-two-numbers?forum=transactsql

declare @mytable table (calls varchar(20), abandoned smallint)

insert into @mytable
values ('Microsoft',   1),
('Apple',       1),
('IBM',         2),
('CISCO',       3)

select calls,1 [Abandoned]  from @mytable t
   cross apply
   ( SELECT DISTINCT number
    FROM master..spt_values
    WHERE number BETWEEN 1 AND t.abandoned
   ) x


-- or simplest

SELECT  t1.calls, 1 [abandoned]
FROM    @mytable t1
JOIN    master.dbo.spt_values t2 on type = 'P' and number < t1.abandoned

> but master.dbo.spt_values number is only up to 2048

CTE would be the best answer.

Thanks. master.dbo.spt_values worked great for me bu tmy data set is large. Tried with cte as Gordon mentioned below but did not give me the full records. — axlrose89, Oct 04 '17 at 21:57

List same row twice if the count is more than 1 in another column

2 Answers2