SQL Server: How to combine rows

Question

I have a query like

select top 10 Col1,Col2,Col3 from tab1

which gives me

(1, 1, 1)
(5, 2, 59)
(8, 3, 69)
(9, 4, 70)
(10, 5, 71)
(11, 6, 72)
(11, 7, 73)
(11, 8, 74)
(11, 9, 75)
(11, 10, 76)

i want to condense the result as

    (1, 1, 1)
    (5, 2, 59)
    (8, 3, 69)
    (9, 4, 70)
    (10, 5, 71)
    (11, 6, 72,73,74,75,76)

how can i do that in the select query itself?

EDIT

note that all of the columns are of int type. in the query result, i would not mind if the third column was cast to varchar

Edit

Ultimately, i am storing the query results in a dataframe. would it be easier to achieve this using dataframes?

Answer 1

You can do this using the technique below. Notice I posted ddl and sample data in a consumable format. You should do this yourself in the future.

if OBJECT_ID('tempdb..#something') is not null
    drop table #something

create table #something
(
    Col1 int
    , Col2 int
    , Col3 int
)

insert #something
select * 
from (Values
(1, 1, 1),
(5, 2, 59),
(8, 3, 69),
(9, 4, 70),
(10, 5, 71),
(11, 6, 72),
(11, 7, 73),
(11, 8, 74),
(11, 9, 75),
(11, 10, 76))x(Col1, col2,col3)

select Col1
    , MIN(Col2) as Col2
    , Stuff((select ',' + cast(Col3 as varchar(4))
        from #something s2
        where s2.Col1 = s.Col1
        for xml path('')), 1,1 , '') as Col3
from #something s
group by Col1