How to do it without the while loop in sql

Question

I have a table like this,

A         |        B
----------------------
1         |       10
1         |       20
2         |       30
2         |       40

I need output as,

A         |         B
------------------------
1         |        10,20
2         |        30,40

Thank you in advance

GROUP BY, and perhaps STUFF or GROUP_CONCAT or similar. Which data type for column B? — jarlh
– jarlh, Commented Jun 4, 2015 at 8:36

Saravana Kumar · Accepted Answer · 2015-06-04 08:45:14Z

3

Try this.

create table #table2(
    col1 int,
    col2 varchar(10),
)

insert into #table2
select 1, '10' union all
select 1, '20' union all
select 1, '30' union all
select 2, '40' union all
select 2, '50' union all
select 2, '60'


select
    col1,
    col2 = 
        stuff((
            select
                ', ' + t2.col2
            from #table2 t2
            where
                t2.col1 = t1.col1
            for xml path(''), type).value('.', 'varchar(max)'
        ), 1, 2, '')
from #table2 t1
GROUP BY t1.col1

edited Jun 4, 2015 at 8:45

answered Jun 4, 2015 at 8:40

Saravana Kumar

3,7295 gold badges19 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

ughai Over a year ago

you don't really need a group by in your inner query in this example. you do need an order by

Danila Ganchar Over a year ago

@ughai sorry. my mistake.not seen tag

Giri Prasad Over a year ago

I cant insert into another table like that because its having too many rows

Saravana Kumar Over a year ago

@Giri Prasad : i haven't understood, explain briefly.

Giri Prasad Over a year ago

@Kumar I cant mention all the rows like you did in your answer coz my table is having many rows

|

Collectives™ on Stack Overflow

How to do it without the while loop in sql

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related