Tuesday, September 24, 2013

SQL Server: Getting comma delimited result set

Below query was asked by velocetti on MSDN SQL Forum, please refer below link for original post.
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)





No comments:

Post a Comment