Solved

CTE Group By One Column and Return Many Column?

Posted on 2014-02-12
2
296 Views
Last Modified: 2014-02-15
There must be a way to Group By 1 column without including every column. I don't want to Group By t1.id or t2.id, only t1.Company_Name .

Note: CTE is just an idea that might work, I don't have CTE code here.

Note: I have more columns not included in this example for brevity sake...

[Table1]
Company_Name           States          Contact_Name
My Business                  KY; OH             John Doe
My Business                  KY; OH             Mark Smith
My Business                  KY; OH             Bill Jones
My Business                  KY; OH             Dan Moore

[Query]
Select
    t1.Company_Name ,
   Stuff ((SELECT '; ' + s.name AS [text()]
           FROM States s
           WHERE (t1.id = s.id) FOR XML path('')), 1, 1, '') AS [States],
 
   Stuff ((SELECT '; ' + c.Contact_Name AS [text()]
           FROM Contacts c
           WHERE (t2.id = c.id) FOR XML path('')), 1, 1, '') AS [Contacts],
From
    Companies t1
    Join Contacts t2 on t1.id = t2.ContactID

Group By
    t1.Company_Name ],   --I only want to Group on this column
    t1.id,                              --don't want to group by
    t2.id                               --don't want to group by
0
Comment
Question by:WorknHardr
2 Comments
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
Comment Utility
Hi WorkN,

Unfortunately, this functionality doesn't exist in SQL Server.  It does in MySQL, but most DBMS haven't incorporated it.

You'll have to run the small query with the correct GROUP BY and then LEFT or INNER join the table back to that result.


Good Luck,
Kent
0
 

Author Closing Comment

by:WorknHardr
Comment Utility
thx
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now