Combining Rows in SQL Output - 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

Combining Rows in SQL Output

Author

Topic

stveater
Starting Member

1 Post

Posted - 2011-09-09 : 18:14:33

I am currently combining data from 3 separate tables and trying to merge the Account Contact Name and Account Contact Email into one row vs. having a row for each contact. Here is the basics of what I am trying to do:

[Table1] Product

ProductID ProductName ProductAvailability ProductAccountID
--------- ----------- ------------------- ----------------
1 Snickers In Stock 123
2 Almond Joy In Stock 456
3 Nerds Out of Stock 789

[Table2] Account

AccountID AccountName AccountLocation
--------- ----------- ----------------
123 Mars USA
456 Hersey's USA
789 Wonka Switzerland

[Table3] AccountContact

AccountContactID AccountContactName AccountContactEmail AccountID
---------------- ------------------ ------------------- ---------
111 Eric Jones eric.jones@mars.com 123
222 Marsha Armstrong marsha.armstrong@mars.com 123
333 Sam Knight sknight@herseys.com 456
444 Phil Star pstar@herseys.com 456
555 Charlie Little charliel@wonka.com 789

Current Query

SELECT
p.ProductName AS 'Product'
,a.AccountName AS 'Account'
,ac.AccountContactName AS 'Account Contact'
,ac.AccountContactEmail AS 'Account Email'

FROM
Product AS p (NOLOCK)

JOIN Account AS a (NOLOCK) ON
a.AccountID=p.ProductAccountID

JOIN AccountContact AS ac (NOLOCK) ON
ac.AccountID=a.AccountID

WHERE
p.ProductAvailability = 'In Stock'

Current Output
Using the above query, I am getting the following output:

Product Account Account Contact Account Email
------- ------- --------------- -------------
Snickers Mars Eric Jones eric.jones@mars.com
Snickers Mars Marsha Armstrong marsha.armstrong@mars.com
Almond Joy Hersey's Sam Knight sknight@herseys.com
Almond Joy Hersey's Phil Star pstar@herseys.com

Exepected Output
I would like to see the Account Contact and Account Email be combined into one row separated by ', '.

Product Account Account Contact Account Email
------- ------- --------------- -------------
Snickers Mars Eric Jones, Marsha Armstrong eric.jones@mars.com, marsha.armstrong@mars.com
Almond Joy Hersey's Sam Knight, Phil Star sknight@herseys.com, pstar@herseys.com

roizanladiero
Starting Member

5 Posts

Posted - 2011-09-09 : 23:37:50

Hi,

Please test this:

SELECT
p.ProductName AS 'Product'
,a.AccountName AS 'Account'
,(ac.AccountContactName + '/' + ac.AccountContactEmail) AS 'ContactName/ContactEmail'

FROM
Product AS p (NOLOCK)

JOIN Account AS a (NOLOCK) ON
a.AccountID=p.ProductAccountID

JOIN AccountContact AS ac (NOLOCK) ON
ac.AccountID=a.AccountID

WHERE
p.ProductAvailability = 'In Stock'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-10 : 00:40:35

[code]
;With CTE()
AS
(
SELECT
p.ProductName AS 'Product'
,a.AccountName AS 'Account'
,ac.AccountContactName AS 'Account Contact'
,ac.AccountContactEmail AS 'Account Email'

FROM
Product AS p (NOLOCK)

JOIN Account AS a (NOLOCK) ON
a.AccountID=p.ProductAccountID

JOIN AccountContact AS ac (NOLOCK) ON
ac.AccountID=a.AccountID

WHERE
p.ProductAvailability = 'In Stock'
)

SELECT Product,Account,
STUFF((SELECT ',' + AccountContactName FROM CTE WHERE Product=c.Product AND Account = c.Account FOR XML PATH('')),1,1,'') AS AccountContacts,
STUFF((SELECT ',' + AccountContactEmail FROM CTE WHERE Product=c.Product AND Account = c.Account FOR XML PATH('')),1,1,'') AS AccountEmails
FROM (SELECT DISTINCT Product,Account FROM CTE)c
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources