T-SQL column value concatination

Question

T-SQL column value concatination

Bhavesh-1094084

SSC Eights!

Points: 865
More actions
July 10, 2009 at 9:37 am

#93776

Hi All,
I have following set of rows
Col1, Col2, Col3, Col4
123, abc, efg, 1111
123, abc, efg, 2222
123, bcd, dff, 3333
124, fer, fds, 5555
I want to write select statement which concatenates Col4 as comma separated string if Col1, Col2, and Col3 are same. After running select statement I should get following result
Col1, Col2, Col3, Col4, NumOfOccurences
123, abc, efg, 1111, 2222 2
123, bcd, dff, 3333 1
124, fer, fds, 5555 1
Please can someone advice best way of acheiving this.
Bhavesh
.NET and SQL Server Blog

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Here is one solution, but I had to upload it as an attachment. I am really getting tired of this intermittent issue of not being able to post code on forums from work.

I'd also like you to pay attention to how I provide the setup code and sample data. This is what you should do when posting questions. For more on that, please read the first article I reference below in my signature block regarding asking for help.

Bhavesh-1094084 SSC Eights! Points: 865 More actions · Answer 2

Thanks for your reply.

Bhavesh
.NET and SQL Server Blog

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Lynn Pettis

SSC Guru

Points: 442467

July 10, 2009 at 10:58 am

#1023218

Let us know if it helps.

Lowell SSC Guru Points: 323518 More actions · Answer 4

Lynn Pettis (7/10/2009)
I am really getting tired of this intermittent issue of not being able to post code on forums from work.

Lynn what browser are you using? is it something rare like Opera Or Google Chrome?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

IE 7. I use IE 7 at home with no problems.

Personally, I think it has something to do with the our network or some piece of hardware used on the netwrok, but I'm not going to complain to Network Services about it.

Bhavesh-1094084 SSC Eights! Points: 865 More actions · Answer 6

Lynn Pettis (7/10/2009)
Let us know if it helps.

Yes this helped but I am not sure about the performance of my query with huge amount of data. I'll post back about my experience.

Bhavesh
.NET and SQL Server Blog

Bhavesh-1094084 SSC Eights! Points: 865 More actions · Answer 7

Lynn Pettis (7/10/2009)
Here is one solution, but I had to upload it as an attachment. I am really getting tired of this intermittent issue of not being able to post code on forums from work.
I'd also like you to pay attention to how I provide the setup code and sample data. This is what you should do when posting questions. For more on that, please read the first article I reference below in my signature block regarding asking for help.

the solution you provided worked for me fine with less data. However it really hits performance when the table contains few thousands of rows. I tested with 1000 initially and the query took 1 min to complete.

I modified the query as below and it gave me huge performance improvement

create table #TestTab (

Col1 int,

Col2 char(3),

Col3 char(3),

Col4 int

);

insert into #TestTab

select 123, 'abc', 'efg', 1111 union all

select 123, 'abc', 'efg', 2222 union all

select 123, 'bcd', 'dff', 3333 union all

select 124, 'fer', 'fds', 5555;

select

tt1.Col1,

tt1.Col2,

tt1.Col3,

( select Convert(VARCHAR, tt2.Col4) + ','

from #TestTab tt2

where

tt2.Col1 = tt1.Col1 and

tt2.Col2 = tt1.Col2 and

tt2.Col3 = tt1.Col3

for xml path('')) as Col4,

count(*) as OccuranceCount

from

#TestTab tt1

GROUP BY tt1.Col1, tt1.Col2, tt1.Col3

drop table #TestTab;

Bhavesh
.NET and SQL Server Blog