CODEWITHSUNDEEP
sql-serverduplicates
Shanka
Shanka

Reputation: 821

Repeat rows based on column value

I have a table like the one below. The column "Repeater" contains both string and integer values and based on this I want to be able to duplicate and repeat the rows. If the "Repeater" value is string, which is always comma separated, then the rows should duplicate based on how many values are there and each row should have one of the values. If the "Repeater" value is an integer then simply it should duplicate the row based on that value.

+---------+--------+-----------------+
| OrderNo | Amount |    RepeaterRes  |
+---------+--------+-----------------+
| AB-001  |    100 | Orange, Apple,  |
| AB-002  |    102 | 3               |
| AB-003  |    980 | Apple           |
| AB-004  |    309 | Banana, Grapes, |
| AB-005  |    840 | 2               |
| AB-006  |    290 | 0               |
| AB-007  |    483 | 0               |
+---------+--------+-----------------+

Output desired:

+---------+--------+----------+
| OrderNo | Amount | Repeater |
+---------+--------+----------+
| AB-001  |    100 | Orange,  |
| AB-001  |    100 | Apple,   |
| AB-002  |    102 | 3        |
| AB-002  |    102 | 3        |
| AB-002  |    102 | 3        |
| AB-003  |    980 | Apple    |
| AB-004  |    309 | Banana,  |
| AB-004  |    309 | Grapes,  |
| AB-005  |    840 | 2        |
| AB-005  |    840 | 2        |
| AB-006  |    290 | 0        |
| AB-007  |    483 | 0        |
+---------+--------+----------+

Upvotes: 1

Views: 615

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81930

Assuming not 2016+

Example

Select OrderNo
      ,Amount
      ,C.*
 from  YourTable A
 Cross Apply ( values (case when try_convert(int,[RepeaterRes]) is null then [RepeaterRes] else replicate([RepeaterRes]+',',IsNull(NullIf(try_convert(int,[RepeaterRes]),0),1)) end )) B(NewString)
 Cross Apply (
                Select Repeater  = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                 From  (Select x = Cast('<x>' + replace((Select replace(NewString,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                 Cross Apply x.nodes('x') AS B(i)
             ) C
 Where C.Repeater is not null

Returns

OrderNo Amount  Repeater
AB-001  100     Orange
AB-001  100     Apple
AB-002  102     3
AB-002  102     3
AB-002  102     3
AB-003  980     Apple
AB-004  309     Banana
AB-004  309     Grapes
AB-005  840     2
AB-005  840     2
AB-006  290     0
AB-007  483     0

Upvotes: 1

Related Questions