SQL Server: Getting comma delimited result set

Below query was asked by velocetti on MSDN SQL Forum, please refer below link for original post.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/0ff51617-03ed-4e52-b116-4ace822d8feb/one-to-many-query-question

Scenario:

I have the following table structure

Subject

SubjectID int

SubjectDetails (varchar(100)

SubjectType

SubjectTypeId (PK)

SubjectID (FK)

SubjectType (varchar(30)

I want to have this type of result set which is essential one Subject row, with SubjectType(s) as another column comma delimited.

So my result set would look like this

SubjectID            SubjectDetails                   SubjectTypes

1                         test details                    SubjectType1, SubjectType2, SubjectType3

How would I accomplish this in TSQL?

Solution:

The above requirement can be achieved by the below query. I have created a function which will concatenate text and separate by comma. 

CREATE TABLE [Subject]

(

      SubjectID INT,

      SubjectDetails VARCHAR(100)

)

CREATE TABLE [SubjectType]

(

      SubjectTypeId INT,

      SubjectID INT,

      SubjectType  VARCHAR(30)

)

INSERT INTO [Subject] (SubjectID, SubjectDetails) VALUES (1, 'test details 1')

INSERT INTO [Subject]  (SubjectID, SubjectDetails)VALUES (2, 'test details 2')

INSERT INTO [SubjectType] (SubjectTypeId, SubjectID, SubjectType) VALUES (1, 1, 'SubjectType1')

INSERT INTO [SubjectType] (SubjectTypeId, SubjectID, SubjectType) VALUES (2, 1, 'SubjectType2')

INSERT INTO [SubjectType] (SubjectTypeId, SubjectID, SubjectType) VALUES (3, 1, 'SubjectType3')

INSERT INTO [SubjectType] (SubjectTypeId, SubjectID, SubjectType) VALUES (4, 2, 'SubjectType4')

INSERT INTO [SubjectType] (SubjectTypeId, SubjectID, SubjectType) VALUES (5, 2, 'SubjectType5')

INSERT INTO [SubjectType] (SubjectTypeId, SubjectID, SubjectType) VALUES (6, 2, 'SubjectType6')

SELECT * FROM [Subject]

SELECT * FROM [SubjectType]

CREATE FUNCTION FnGetConcatenate(@SubjectID INT)

RETURNS VARCHAR(500) 

AS

BEGIN 

      DECLARE @String VARCHAR(500) = ''

     

      SELECT @String = @String + SubjectType + ', '

            FROM SubjectType WHERE SubjectID = @SubjectID

     

      RETURN @String

END

SELECT S.SubjectID, S.SubjectDetails, dbo.FnGetConcatenate(S.SubjectID)

      FROM [Subject] S

      JOIN SubjectType ST ON S.SubjectID = ST.SubjectID

      GROUP BY S.SubjectID, S.SubjectDetails, dbo.FnGetConcatenate(S.SubjectID)