T-SQL concatenate rows into string

Question

I'm trying to use a sub query on a select statement for a field value but I can't seem to figure out the correct syntax. I want to pull a list of company names and as a field for that query, I want to select all the employees for that company.

Any ideas on what I'm doing wrong? The error I get is

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

T-SQL code:

SELECT 
   company_name, 
   company_type, 
   (SELECT 
        employee_firstname, employee_lastname 
    FROM 
        tblemployees 
    WHERE 
        tblemployees.company_id = tblCompanies.company_id) as employees 
FROM 
    tblCompanies

Desired output:

Company Name |  Company Type  | Employees
----------------------------------------------------------
Test Co      |  Construction  | Bob Smith, Jack Smith, etc

possible duplicate of Concatenate many rows into a single text string? — Jeremy
– Jeremy, Commented Aug 30, 2013 at 15:45

Bryan · Accepted Answer · 2013-08-30 15:58:33Z

9

You'll need to concatenate the first and last names using FOR XML PATH or a similar solution. More details on the various methods here.

SELECT DISTINCT
   c1.company_name, 
   c1.company_type,
   STUFF((SELECT
              ', ' + c2.employee_firstname + ' ' + c2.employee_lastname
          FROM
              tblCompanies c2
          WHERE
              c1.company_id = c2.company_id
          ORDER BY
              employee_lastname, employee_firstname
          FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')
FROM tblCompanies c1

SQL Fiddle

edited Aug 30, 2013 at 15:58

answered Aug 30, 2013 at 15:51

Bryan

17.7k7 gold badges59 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ralph Over a year ago

Thank you sir for that information. That worked perfectly, extremely help. Thanks again.

Collectives™ on Stack Overflow

T-SQL concatenate rows into string

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related