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?

I don't think this is possible in a single select query, since a select query has a definite set of returned columns for all rows. — René Vogt, Dec 01 '15 at 17:02
so will i need to create a temp table? would it be easier to do this in python's dataframe? — AbtPst, Dec 01 '15 at 17:03
@RenéVogt this can be done in a single query using the XML PATH technique. — Sean Lange, Dec 01 '15 at 17:08
do you want `(11, 6, '72,73,74,75,76')` group those in one string?? — Juan Carlos Oropeza, Dec 01 '15 at 17:09
@SeanLange I commented before the edit saying it's ok to combine the third column as a string. After this edit it was a completely different question. — René Vogt, Dec 01 '15 at 17:13
Not sure why the downvote but this kind of thing has been done hundreds of times around here. :) — Sean Lange, Dec 01 '15 at 17:23

score 2 · Accepted Answer · answered Dec 01 '15 at 17:10

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

The varchar need to be long enough to hold your int values. I don't know what values are in your actual data. From your sample it needs to be at least 2 but I chose 4 which will handle values up to 9999. Feel free to adjust that size to suit your actual data requirements. — Sean Lange, Dec 01 '15 at 17:22

SQL Server: How to combine rows

1 Answers1