Tutorial :T-SQL for combining 2 columns' values into one with “replicate”



Question:

There is a table with values like this:

Sets | Reps  -----+------  2    | 10  3    | 15  3    | 12  4    | 8  

I need to combine those two values into one in my SELECT statement, so I could get

SELECT ... AS Reps FROM Table    Reps  ------------  10,10  15,15,15  12,12,12  8,8,8,8  

Any ideas how to do this?

Testing script:

DECLARE @Test TABLE ([Sets] INT, [Reps] INT);    INSERT INTO @Test VALUES (2, 10);  INSERT INTO @Test VALUES (3, 15);  INSERT INTO @Test VALUES (3, 12);  INSERT INTO @Test VALUES (4, 8);    SELECT [Sets], [Reps] FROM @Test;    // Here is an answer by tpdi:  SELECT CAST([Reps] AS VARCHAR) +         REPLICATE(',' + CAST([Reps] AS VARCHAR), [Sets] - 1)         AS [Reps] FROM @Test;  


Solution:1

Like this:

select substring(replicate(','+cast(Reps as varchar),Sets),2,8000) as Reps  from Table  


Solution:2

select cast(reps as varchar) + replicate( ',' + cast(reps as varchar), sets - 1) from table


Solution:3

select SUBSTRING(REPLICATE(CAST([Reps] as varchar)+',' , [Sets]), 0, LEN(REPLICATE(CAST([Reps] as varchar)+',' , [Sets])) ) as [Reps] from @Test


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »