2

I have a stored procedure which returns a weekly result from input parameters start date and end date. I am getting multiple rows in the result.

Requirement: I want to add the results of columns in a single column. Below is my final query. From this I want to concatenate the result:

SET @sqlText = 
    N'SELECT ['+REPLACE(@columns,',','],[') + ']' +
    ' 
    FROM dbo.timesheet 
    WHERE
        month ='''+ @strMonth+''' 
        AND [Task ID] IN
        (
            SELECT TaskID 
            FROM ManageTasks 
            WHERE
                TeamMemberUserID ='+  Cast(@UserID AS VARCHAR(max)) +
        ')'

--Here I want to add the columns result and need to show as single column

PRINT @sqlText
EXEC (@sqlText)
0

2 Answers 2

3

Use FOR XML PATH

Sample from TSQL – Concatenate Rows using FOR XML PATH() - sqlandme.com

SELECT      CAT.Name AS [Category],
            STUFF((    SELECT ',' + SUB.Name AS [text()]
                        — Add a comma (,) before each value
                        FROM Production.ProductSubcategory SUB
                        WHERE
                        SUB.ProductCategoryID = CAT.ProductCategoryID
                        FOR XML PATH('') — Select it as XML
                        ), 1, 1, '' )
                        — This is done to remove the first character (,)
                        — from the result
            AS [Sub Categories]
FROM  Production.ProductCategory CAT

Without FOR XML PATH

enter image description here

With FOR XML PATH

enter image description here

0

It sounds like a task for the presentation layer and not something that should not be done using SQL Server...

Having said that, you can use an undocumented side-effect like this:

DECLARE @MyConcatString VARCHAR(MAX) = ''
SELECT @MyConcatString = @MyConcatString + name+ ',' FROM sys.objects
PRINT @MyConcatString

To concatenate the name from all rows in sys.objects into one column/variable.

0

Not the answer you're looking for? Browse other questions tagged or ask your own question.