Concating SQL Tables but only retrieving one?

Question

I have three tables related to this SQL statement.

Project

• ProjectRecordId
• ProjectName

Project_CompetitionCategory

• ProjectCompetitionCategoryRecordId
• ProjectRecordId
• CompetitionCategoryRecordId

CompetitionCategory

• CompetitionCategoryRecordId
• CompetitionCategoryName

What I am trying to retrieve from my tables is ProjectRecordId, ProjectName, CompetitionCategoryName values.

However a Project may be taking part in more than one Competition Category. I need the information to be in a single row and the competitions to be in it's own column.

Example below

1. 1 | Project No 1 | Competition 1, Competition 2
2. 2 | Project No 2 | Competition 2, Competition 3, Competition 4
3. 3 | Project No 3 | Competition 1, Competition 4

This is the current SQL statement that I have:

DECLARE @Data VARCHAR(2000)
DECLARE @pID INT
DECLARE @pName VARCHAR(300)

SELECT 
    @pID = Project.ProjectRecordId, 
    @pName = Project.ProjectName,@Data = COALESCE(@Data + ',' + CompetitionCategoryName, CompetitionCategoryName)
FROM 
    Project_CompetitionCategory 
INNER JOIN
    CompetitionCategory ON Project_CompetitionCategory.CompetitionCategoryRecordId = CompetitionCategory.CompetitionCategoryRecordId 
INNER JOIN
    Project ON Project_CompetitionCategory.ProjectRecordId = Project.ProjectRecordId
WHERE 
    Project.ProjectRecordId = 10

SELECT 
   @pID AS 'Project Record ID',  
   @pName AS 'Project Name', 
   @Data AS Competition, 
   (SELECT COUNT(ProjectRecordId) FROM Presentation WHERE ProjectRecordId = @pID) AS 'Number of Recorded Presentations'

If I remove

WHERE Project.ProjectRecordId = 10 

then it only fetches the last value and the CompetitionCategory is then messed up.

Something like below.

1. 15 | Project No 15 | Competition1, Competition 2, Competition 3, Competition 2, Competition 4

I have read various answers to this stackoverflow article Concatenate many rows into a single text string?

and googled finding a solution on sites such as

1. http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html
2. http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

However I still cannot find a solution to it. I'm using SQL Server 2008 R2. I'm new to SQL and would like to understand why it's not possible to get the data that I want.

Thanks in advance.

Answer 1

The table structure you have is not well designed to query for something like that. Rather than concatenate with the query, is there any reason you can't consume a one-to-many relationship result as follows:

SELECT p.ProjectRecordId, p.ProjectName, cc.CompetitionCategoryName FROM Project p JOIN Project_CompetitionCategory pcc on p.ProjectRecordId = pcc.ProjectRecordId JOIN CompetitionCategory cc on pcc.CompetitionCategoryRecordId = cc.CompetitionCategoryRecordId

The query you mention will concatinate the table for your selected projectRecordID, but removing the recordId will simply list all the categories possible in the table for all projects.

DECLARE @compCat VARCHAR(1000)

SELECT @compCat = COALESCE(@compCat + ',', '') + cc.CompetitionCategoryName 
FROM Project p JOIN Project_CompetitionCategory pcc on p.ProjectRecordId = pcc.ProjectRecordId JOIN CompetitionCategory cc on pcc.CompetitionCategoryRecordId = cc.CompetitionCategoryRecordId WHERE p.ProjectRecordId=10

At this point @compCat will have only the competition categories for project 10. Removing the project 10 filter will result in a list of all project's categories. In order to get the concatenated result this way, you will need to write sql with cursors that selects the projectId and passes it to the concatenation query... something like the below:

DECLARE @compCat VARCHAR(1000)
DECLARE projectIdSelect CURSOR FOR SELECT ProjectRecordId FROM Project

OPEN projectIdSelect
FETCH NEXT FROM projectIdSelect INTO @projectID
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @compCat = ''
    SELECT @compCat = COALESCE(@compCat + ',', '') + cc.CompetitionCategoryName 
    FROM Project p JOIN Project_CompetitionCategory pcc on p.ProjectRecordId = pcc.ProjectRecordId JOIN CompetitionCategory cc on pcc.CompetitionCategoryRecordId = cc.CompetitionCategoryRecordId WHERE p.ProjectRecordId=@vars
    PRINT('Project ID: ' + @vars + ' | ' + @compCat)    
END
CLOSE projectIdSelect 
DEALLOCATE projectIdSelect