CODEWITHSUNDEEP
sqlsql-serverentity-frameworkt-sql
Shawn
Shawn

Reputation: 353

TSQL + Entity Framework Join tables and concatenate rows into one string column

I am trying to join 3 tables in a query to get a cohesive output that summarizes the employees and their titles. The setup is as below:

CREATE TABLE employees (id INT, name VARCHAR(16))
CREATE TABLE titles (employeeId INT, standardTitleId INT)
CREATE TABLE standard_titles(standardTitleId INT, title VARCHAR(16))
GO

INSERT INTO standard_titles VALUES
    (0, 'Co-Founder'),
    (1, 'CEO'),
    (2, 'CTO'),
    (3, 'CFO')
INSERT INTO employees VALUES 
    (0, 'Bill'),
    (1, 'Bob'),
    (2, 'Sue')
INSERT INTO titles VALUES
    (0, 0),
    (0, 1),
    (1, 0),
    (1, 2),
    (2, 3)
GO

My desired output would be something like this:

id    name      titles
================================
0     Bill      Co-Founder, CEO
1     Bob       Co-Founder, CTO
2     Sue       CFO

After reading through posts, I have managed to come close but also managed to finagle something:

SELECT
    e.id,
    e.name, 
    STUFF((
        SELECT ', ' + s.title
        FROM standard_titles s
            INNER JOIN titles t ON t.standardTitleId = s.standardTitleId
            INNER JOIN employees e ON e.id = t.employeeId
        WHERE t.standardTitleId = s.standardTitleId
        FOR XML PATH ('')), 1, 1, '')
FROM employees e

which gives me this:

id  name    (No column name)
===========================================================
0   Bill       Co-Founder, CEO, Co-Founder, CTO, CFO
1   Bob        Co-Founder, CEO, Co-Founder, CTO, CFO
2   Sue        Co-Founder, CEO, Co-Founder, CTO, CFO

The questions:

  1. What I am doing wrong in the FOR XML PATH part.

  2. Once the TSQL is fixed, is there a way to do this via Entity Framework?

Upvotes: 1

Views: 514

Answers (2)

Lamak
Lamak

Reputation: 70648

So, here is half an answer, only for your first question:

SELECT
    e.id,
    e.name, 
    STUFF((
        SELECT ', ' + s.title
        FROM standard_titles s
            INNER JOIN titles t ON t.standardTitleId = s.standardTitleId
            --INNER JOIN employees e ON e.id = t.employeeId
        WHERE e.id = t.employeeId
        FOR XML PATH ('')), 1, 1, '')
FROM employees e

And here is a demo for you to try.

Upvotes: 2

HLGEM
HLGEM

Reputation: 96570

try this:

SELECT
    e.id,
    e.name, 
    STUFF((
        SELECT ', ' + s.title
        FROM standard_titles s
            INNER JOIN titles t ON t.standardTitleId = s.standardTitleId
        WHERE e.id = t.employeeId
        FOR XML PATH ('')), 1, 1, '')
FROM employees e

You were not correlating to the outer employees table.

Upvotes: 1

Related Questions