How to combine the column data

1

I have a table with 2 columns Col1 & Col2, and it has 2 rows.
I want to show only one record. Please find the below example

Col1 Col2
A abc
A xyz

Expected Result:

Col1 Col2

A abc, xyz

2

;with cte as
(select s = Col1 + ' ' + Col2, i = row_number() over (order by col1+col2) from tbl
)
select col1 = (select s from cte where i = 1)
, col2 = (select s from cte where i = 2)

Haven't checked this as it doesn't look like a complete question.

3

Thanks for your reply nigelrivett,

I am expecting

Col1 - A
Col2 - abc, xyz

4

select col1, col2 = min(Col2) + ', ' + max(Col2)
from tbl
group by col1

Still don't think there is more to the requirement.

5

great, this is what i am expecting,
Thanks for your great support

6

Hi nigelrivett,
I have one more query could you please help me

Query: I have a Table with One column and 20 rows, First 10 rows are 'abc' (abc repeating 10 times), and remaining 10 rows are 'xyz',

Col1

abc
abc
abc
abc
.
.
.
.
abc(10th row)
xyz
xyz
xyz
xyz
.
.
.
xyz(20th row)

I want to display abc 3 times and xyz 3 times (total 6rows)

7 

;with cte as
(
select Col1, i = row_number() over (partition by Col1 order by Col1)
from tbl
)
select Col1 
from cte 
where i <= 3 
order by Col1
8

Thank you so much, I got it
One more query I have,
i) How to delete second duplicate value ( I want to keep it first duplicate value)