SQL - Multiple rows into single column

Question

I would like to take the data output from the query below and join all of the email addresses together separated by a semicolon grouped by the employee name.

SELECT
    DISTINCT
    p.email
    , e.name 
FROM
    PERSON p
INNER JOIN 
    EMPLOYEE e
ON 
    p.agentofrecord_id = e.employee_id 
WHERE 
    dbo.GetPersonMember(p.person_id) = 1
    AND (p.isactive = 1)
    AND p.email <> ''
ORDER BY name

See http://stackoverflow.com/questions/905818/tsql-comma-separation/913023#913023 — van
– van, Commented Jun 18, 2009 at 5:08
You are asking how to violate 1NF, which SQL isn't really designed to do e.g. the SQL Standard lacks a 'Concatenate' set function. en.wikipedia.org/wiki/First_normal_form#Domains_and_values. — onedaywhen
– onedaywhen, Commented Jun 18, 2009 at 7:24
@onedaywhen - This is to get email addresses in a usable format grouped by employee... Nothing else... If I wasn't doing it here, it'd be code or excel... Just looking for something easy to save some time later. — RSolberg
– RSolberg, Commented Jun 18, 2009 at 15:12

Alex Martelli · Accepted Answer · 2009-06-18 05:04:55Z

5

Basically, it looks like you want MySql's GROUP_CONCAT aggregate function in TSQL. If that's the case, this article may help -- check it out!

answered Jun 18, 2009 at 5:04

Alex Martelli

887k175 gold badges1.3k silver badges1.4k bronze badges

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

Collectives™ on Stack Overflow

SQL - Multiple rows into single column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related