How can I make duplicate data rows into a single column

Question

I have a query that is returning project IDs, project names, and project types. Some projects have multiple types, so I am getting the same project ID more than once for some because they will have more than one project type. Many projects only have one type though.

Example of what it's doing:

| Project_ID |   Project_Name    | Project_Type  |
|   94850    |   Convert to C#   |    .Net       |
|   94850    |   Convert to C#   |     SQL       |

Here is what I want it to do:

| Project_ID |   Project_Name    | Project_Type  |
|   94850    |   Convert to C#   |  .Net, SQL    |

I do not want anything but the Project Type to be pivoted this way. Would this be something for PIVOT or something else?

SELECT PL.Project_ID,
       PL.Project_Name,
       PT.Project_Type
FROM   Project_List PL
INNER JOIN Project_Types PT ON PL.Project_ID = PT.Project_ID
WHERE  ProjectDate BETWEEN (@Start) AND (@End)

Answer 1

You can use a STUFF() function for your purpose (https://msdn.microsoft.com/en-us/library/ms188043.aspx).

CREATE TABLE #temp (
    Project_ID int,
    Project_Name nvarchar(255),
    Project_Type nvarchar(255)
);
GO
INSERT INTO #temp 
SELECT 94850, 'Convert to C#', '.Net' UNION ALL
SELECT 94850, 'Convert to C#', 'SQL' UNION ALL
SELECT 94851, 'Convert to Java', 'Java'
GO

SELECT 
    Project_ID,
    Project_Name,
        STUFF((SELECT ',' + Project_Type FROM #temp tt Where tt.Project_ID = t.Project_Id
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM #temp t
GROUP BY Project_ID, Project_Name;

DROP table #temp;

result:

Project_ID  Project_Name    Project_Types
94850   Convert to C#   .Net,SQL
94851   Convert to Java Java