CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Tarkus
Tarkus

Reputation: 234

T-SQL for combining 2 columns' values into one with "replicate"

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;

Upvotes: 1

Views: 1449

Answers (3)

Ravee
Ravee

Reputation:

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

Upvotes: 0

tpdi
tpdi

Reputation: 35171

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

Upvotes: 2

Guffa
Guffa

Reputation: 700622

Like this:

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

Upvotes: 2

Related Questions