Concatenate strings in a windowing fuction

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Concatenate strings in a windowing fuction

Author

Topic

ATG
Starting Member

35 Posts

Posted - 2011-07-12 : 13:06:29

I'm trying to concatenate strings in a windowing function.

For example, I have a table that has four lines that share a common link:

Name Cars
Bob Toyota
Bob Honda
Bob Chevy
Bob Maserati

I'd like the return value to be Toyota,Honda,Chevy,Maserati on one line using Bob as the Partition By.

Is this possible?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-12 : 13:22:02

As far as I know... Partition By will NOT be helpful.

But...


Declare @t table (
	Name varchar(10),
	Cars varchar(10)
)

Insert Into @t Select 'Bob','Toyota'
Insert Into @t Select 'Bob','Honda'
Insert Into @t Select 'Bob','Chevy'
Insert Into @t Select 'Bob','Maserati'


Select 
	o.Name, 
	Left(a.cars,LEN(a.cars)-1) 
From (Select name From @t Group By Name) o
Cross Apply
	(
	Select Cars+', '
	From @t i
	Where i.Name = o.Name
	For Xml Path('')
	) a(Cars)

Corey

I Has Returned!!

ATG
Starting Member

35 Posts

Posted - 2011-07-12 : 14:30:48

That's too bad. I've used PARTITION BY to sum by like fields, so I assumed there was an equivalent and elegant way of concatenating strings... Thanks for your reply!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-07-14 : 05:45:46

Another stuff


Declare @t table (
	Name varchar(10),
	Cars varchar(10)
)

Insert Into @t Select 'Bob','Toyota'
Insert Into @t Select 'Bob','Honda'
Insert Into @t Select 'Bob','Chevy'
Insert Into @t Select 'Bob','Maserati'

Select 
	distinct o.Name, 
	stuff
	(
		(
			Select ','+Cars
			From @t i
			Where i.Name = o.Name
			For Xml Path('')
		),1,1,''
	) as cars
from @t as o

Madhivanan

Failing to plan is Planning to fail

ATG
Starting Member

35 Posts

Posted - 2011-07-14 : 18:17:59

Thanks for the replies! This is what I ended up doing and it worked just fine.

stuff((select ', ' + t.HoldCode
from APHD t
where t.APLine=APHD.APLine and t.APSeq=APHD.APSeq and t.APTrans=APHD.APTrans and t.Mth=APHD.Mth and t.APCo=APHD.APCo
order by APHD.HoldCode
for xml path('')),1,1,'')

as [HoldCodes],

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources