1

Possible Duplicate:
Concatenate row values T-SQL

I'm new to SQL Server and had tried few techniques that suggested from internet like using temp variable, XML path, COALESCE and etc but all doesn't meet my requirement somehow.

I'm using Toad for SQL Server version 5.5 to create SQL script and the account I used to query DB server only got READ access. Hence can't use CREATE VIEW statement I believe.

Table Name : Customer

ServerName  Country  contact
----------  -------  -------------
srv1        SG       srv1_contact1
srv1        SG       srv1_contact2
srv1        SG       srv1_contact3
srv2        HK       srv2_contact1
srv2        HK       srv2_contact2
srv3        JP       srv3_contact1
srv3        JP       srv3_contact2
srv3        JP       srv3_contact3
srv4        KR       srv4_contact1

Expected output:

ServerName  Country  contact
----------  -------  -------------------------------------------
srv1        SG       srv1_contact1; srv1_contact2; srv1_contact3
srv2        HK       srv2_contact1; srv2_contact2
srv3        JP       srv3_contact1; srv3_contact2; srv3_contact3
srv4        KR       srv4_contact1
Community
  • 1
  • 1
user1486134
  • 35
  • 2
  • 2
  • 5
  • 1
    Not sure how a view would help. Also if you post the XML path query you tried we can probably tell you why it didn't meet your requirement (we don't know what "somehow" means). – Aaron Bertrand Jun 27 '12 at 16:25
  • See http://stackoverflow.com/questions/1874966/concatenate-row-values-t-sql for a much better set of solutions. – David Manheim Jun 29 '12 at 15:31

1 Answers1

7
SELECT ServerName, Country, contact = STUFF((SELECT '; ' 
    + ic.contact FROM dbo.Customer AS ic
  WHERE ic.ServerName = c.ServerName AND ic.Country = c.Country
  FOR XML PATH(''), TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 2, '')
FROM dbo.Customer AS c
GROUP BY ServerName, Country
ORDER BY ServerName;
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • thanks for the replye @Aaron. getting similar error when using XML PATH. ERROR MSG "Lookup Error - SQL Server Database Error: Line 7: Incorrect syntax near 'XML'." **** MY XML PATH START HERE **** SELECT DISTINCT STUFF ( (SELECT ', ' + contact FROM customer FOR XML PATH ( '' )), 1, 1, '') AS contact FROM customer **** MY XML PATH END HERE **** – user1486134 Jun 28 '12 at 02:51
  • Maybe this is a problem with TOAD, since that doesn't look like a SQL Server error (and I tested this code). Please try running your code from Management Studio, or put your code in a stored procedure and call *that* from TOAD. – Aaron Bertrand Jun 28 '12 at 03:05