GROUP BY and listing rows in an aggrigate

[nallelcm]

here is my problem.

table: Employees
id name position

1 bob sales
2 joe sales
3 mark management
4 jane management
5 julie it
6 donald sales

what I want to do is write a query that would spit out

position people

sales bob, joe, donald
management mark, jane
it julie

so something like this

SELECT position, somefunction(names) AS people FROM Employees GROUP BY position

does that 'somefuction' exist? if not how would i emulate it. I'm doing this for reporting purposes.

[Lost User]

nallelcm wrote:

here is my problem.

I included a script that generates your demo-data; just copy and paste to give it a try.

-- Just to be on the safe side
BEGIN TRANSACTION

-- The sample table
CREATE TABLE Employees
(
id INT
,[name] VARCHAR(50)
,position VARCHAR(50)
);

-- The sample data
INSERT INTO Employees (id, [name], position)
SELECT 1, 'bob', 'sales'
UNION SELECT 2, 'joe', 'sales'
UNION SELECT 3, 'mark', 'management'
UNION SELECT 4, 'jane', 'management'
UNION SELECT 5, 'julie', 'it'
UNION SELECT 6, 'donald', 'sales'

-- Making a list of unique positions
SELECT DISTINCT position
INTO #Positions
FROM Employees;

-- Select all positions, using a FOR XML subquery to fetch the members
-- The REPLACE and SUBSTRING functions are used to remove the XML-tags
SELECT position,
SUBSTRING(names, 0, LEN(names) - LEN(', '))
FROM (SELECT position,
REPLACE(REPLACE((SELECT [name]
FROM employees e
WHERE e.position = p.position
FOR XML AUTO), '', ', ') AS
names
FROM #positions p) AS tmp_cte;

-- Undo everything we just did
ROLLBACK

Output on my machine;

position names

---

it julie
management mark, jane
sales bob, joe, donald

Bastard Programmer from Hell :suss:

[nallelcm]

Ok, well how well would this translate into this situation. I never really did explain myself very well.. here is a better example EventType

ID Name

1 Party
2 Meeting

Person

ID Name

1 Bob
2 Joe
3 Suzie

Event

UID EventID EventType Person

1 1 1 1
2 1 1 2
3 1 1 3
4 2 1 1
5 2 1 3
6 3 2 1
7 3 2 2

Want the output to look like

EventID EventTypeName People

1 Party Bob, Joe, Suzie
2 Party Bob, Suzie
3 Meeting Bob, Joe

[Lost User]

nallelcm wrote:

here is a better example

That's not a better example, but a different one. It would have been better if it included a script to generate the sample data :)

nallelcm wrote:

Want the output to look like

I want food. Back in a bit :)

Bastard Programmer from Hell :suss:

[Lost User]

The same trick, basically;

BEGIN TRANSACTION
CREATE TABLE #EventType
(
ID INT
,[Name] VARCHAR(50)
)
INSERT INTO #EventType
SELECT 1 ,'Party'
UNION SELECT 2 ,'Meeting'
UNION SELECT 3 ,'Something else that wasn''t mentioned'

CREATE TABLE #Person
(
ID INT
,[Name] VARCHAR(20)
)
INSERT INTO #Person
SELECT 1 ,'Bob'
UNION SELECT 2 ,'Joe'
UNION SELECT 3 ,'Suzie'

CREATE TABLE [#Event]
(
UID INT
,EventID INT
,EventType INT
,Person INT
)
INSERT INTO [#Event]
SELECT 1, 1, 1, 1
UNION SELECT 2, 1, 1, 2
UNION SELECT 3, 1, 1, 3
UNION SELECT 4, 2, 1, 1
UNION SELECT 5, 2, 1, 3
UNION SELECT 6, 3, 2, 1
UNION SELECT 7, 3, 2, 2

SELECT DISTINCT EventId
INTO #SomeTable
FROM [#Event]

SELECT EventId
,[Name]
,SUBSTRING(CompoundColumn, 0, LEN(CompoundColumn) - LEN(', '))
AS People
FROM (
SELECT ST.EventId
,ET.[Name]
,REPLACE(REPLACE(
(SELECT P.[Name]
FROM #Event E
JOIN #Person P ON E.Person = P.ID
WHERE E.EventID = ST.EventID
FOR XML AUTO),
'

', ', '
) AS CompoundColumn
FROM #SomeTable ST
LEFT JOIN #EventType ET ON ST.EventId = ET.ID
) AS tmp_cte

ROLLBACK

Desired result

EventID EventTypeName People

1 Party Bob, Joe, Suzie
2 Party Bob, Suzie
3 Meeting Bob, Joe

Result on my machine

EventId Name People

---

1 Party Bob, Joe, Suzie
2 Meeting Bob, Suzie
3 Something else that wasn't mentioned Bob, Joe

Are you sure that your sample output is correct? :)

Bastard Programmer from Hell :suss:

[nallelcm]

Thank you!! in the Event the EventID is a UID for the specific event. EventType is a FK to the EventType table. So there should be 2 parties (event id 1 and 2) and 1 meeting (event id 3) I need to look up how this XML stuff works :/

[Lost User]

My pleasure :)