-1

Possible Duplicate:
Concatenate many rows into a single text string?

I have 2 tables in SQL Server 2008 database - tblQuestion and tblSummary. I'm selecting QuestionIDs from tblQuestion as follows.

SELECT QuestionId from tblQuestion WHERE Status='Completed';

The result is as follows (from multiple records),

QuestionId 
----------
1
2
5
7
8
9

[6 rows]

Now, I need to insert above selected IDs to "CompletedSections" column (which is VARCHAR type) in tblSummary. It should be inserted as a CSV like format - 1,2,5,7,8,9

For example, if I select those from tblSummary will be as follows.

SELECT CompletedSections FROM tblSummary WHERE <Some Condition>

Result should be,

CompletedSections 
-----------------
1,2,5,7,8,9

[1 row]

How this can be done at the database level using t-SQL (not using any programming language like C#)? I'm hoping to implement this with t-SQL using a scheduled SQL SP/ Function/ Trigger.

Thanks,

Chatur

Community
  • 1
  • 1
chatura
  • 4,097
  • 4
  • 19
  • 19
  • 4
    Why, oh why, do you want to store these as CSV? This is like throwing all your bowls, forks and glasses in a single drawer. – Aaron Bertrand Nov 20 '12 at 15:48
  • see the following link so something similar http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string – cardano dev 12 Nov 20 '12 at 15:53

3 Answers3

2

Here is a solution I have used in the past. It is a little hacky, but it should be faster than using a cursor to create the CSV. Here is some example code to get you started.

DECLARE @tblQuestion TABLE
(
     QuestionId nvarchar(10)
)

DECLARE @tblSummary TABLE
(
    CompletedSections nvarchar(100)
)

INSERT INTO @tblQuestion
VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9)

INSERT INTO @tblSummary
SELECT SUBSTRING(
(SELECT ',' + QuestionId 
from @tblQuestion 
FOR XML PATH('')),2,200000) AS CSV

SELECT * FROM @tblSummary
Brad Ingram
  • 181
  • 4
1

This will do the job;

DECLARE @S VARCHAR(5000)

SELECT @S=ISNULL(@S+ ',', '') + CONVERT(VARCHAR(10),QuestionId)
FROM tblQuestion 

SELECT @S
Kaf
  • 33,101
  • 7
  • 58
  • 78
-2
    DECLARE @STRING VARCHAR(MAX) = ''
    DECLARE @VALUE VARCHAR(MAX)

    DECLARE A_CURSOR CURSOR FOR
    SELECT QuestionId from tblQuestion WHERE Status='Completed'

    OPEN A_CURSOR
    FETCH A_CURSOR INTO @VALUE

    WHILE @@FETCH_STATUS = 0
    BEGIN

    if @STRING <> ''
    set @STRING = @STRING + ', '

    set @STRING = @STRING + CONVERT(VARCHAR(MAX),@VALUE)

    FETCH A_CURSOR INTO @VALUE

    END

    CLOSE A_CURSOR

    DEALLOCATE A_CURSOR

    INSERT INTO CompletedSection (tblSummary) SELECT @STRING
rufu5
  • 66
  • 6