SQL JOIN - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

SQL JOIN

Author

Topic

saminda
Starting Member

10 Posts

Posted - 2009-07-16 : 21:19:24

I have a table where primary key is a combination of two columns(customerno & cur) and has records as follows,

customerno cur bank
3671079 AUD HONGKONG BANKING CORPORATION LIMITED, THE AUD
3671079 EUR HONGKONG BANKING CORPORATION LIMITED, THE
3671079 USD SHANGHAI BANKING CORPORATION LIMITED, THE

And when I join columns customerno & bank of this with another table where customerno alone is the pirmary key, i get all the above 3 records.

Is it possible to put all these 3 records into one, just like below?

customerno bank
3671079 HONGKONG BANKING CORPORATION LIMITED, THE AUD HONGKONG BANKING CORPORATION LIMITED, THE SHANGHAI BANKING CORPORATION LIMITED, THE

This problem is really troubling me and any advice regarding this is highly appreciated.

Thanking you
Saminda

ras

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-16 : 21:27:35

if you are using
SQL 2000 : Rowset string concatenation: Which method is best ?
SQL 2005 /2008 : concatenate records without UDF

KH
[spoiler]Time is always against us[/spoiler]

saminda
Starting Member

10 Posts

Posted - 2009-07-16 : 22:01:38

Khtn,
Thanks a lot for your prompt reply.
I just copy-paste the SQL2005/2008 solution, BUT its giving syntaxt erros near FOR & XMl.

Thanking you
saminda

ras

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-16 : 22:10:05

you are using SQL Server 2005 / 2008 ? What is the compatibility level ? Is it 90 / 100 ?

KH
[spoiler]Time is always against us[/spoiler]

saminda
Starting Member

10 Posts

Posted - 2009-07-16 : 22:43:53

I am using sql server 2005
I dont know how to check compatibility level.

thanks
saminda

ras

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-16 : 22:48:05

[code]
sp_dbcmptlevel 'database_name'
[/code]

you need compatibility level 90 and above to use the "FOR XML".

If yours is already at 90, post your query here, we will have a look at it

KH
[spoiler]Time is always against us[/spoiler]

saminda
Starting Member

10 Posts

Posted - 2009-07-16 : 22:57:24

khtan,
database level is 80. Thats why FOR XML didnt work in the first place.
Any other way to achive this ?

thanking you
saminda

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-16 : 23:11:56

one way is to change to compatibility level 90 or use the SQL 2000 method. See the other link that i posted

KH
[spoiler]Time is always against us[/spoiler]

saminda
Starting Member

10 Posts