Iterating through a SQL Server 2008 R2 table variable and concatenate values

Question

I have a table variable @Holding two columns: an id (not unique) and a message:

id     message
----   -------
2      give
2      me
2      help 
3      Need
3      help
1      help!

The result should be

2     give me help
3     Need help
1     help!

This it very much simplified, but shows that there are id which may exist more than once, and some kind of text which should be concatenated into a string.

I cannot manage it to loop through this table variable (but not through a table too!).

I tried a cursor (which I did not understand correctly) but it failed of course. The number of records are not that much, not even 100 in that table variable.

Thanks yr. help Michael

Which RDBMS? oracle, MySQL? SQL SEerver? mySQL can do this using group_concat, oracle WM_CONCAT, and maybe for xml path in SQL Server. but this wouldn't guarantee order, there's no implied order in the above... — xQbert, May 01 '15 at 19:19
possible duplicate of [SQL group\_concat function in SQL Server](http://stackoverflow.com/questions/8868604/sql-group-concat-function-in-sql-server) Again though order of the text in the message is not guaranteed! or dup of http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server — xQbert, May 01 '15 at 19:29
possible duplicate of [Concatenate values based on ID](http://stackoverflow.com/questions/6603319/concatenate-values-based-on-id) — Rocketq, May 01 '15 at 19:34

ZeRaTuL_jF · Answer 1 · 2015-05-01T19:38:23.923

0

This should work if you are using SQL Server.

 select T1.id,
   stuff((select ' '+T2.[message]
          from @A as T2
          where T1.id = T2.id
          for xml path(''), type).value('.', 'varchar(max)'), 1, 1, '') as [message]
 from @A as T1
 group by T1.id

edited May 01 '15 at 19:38

answered May 01 '15 at 19:29

ZeRaTuL_jF

582
2
4
20

score 0 · Answer 2 · edited May 23 '17 at 11:58

0

Original question

No CURSOR, WHILE loop, or User-Defined Function needed.

Just need to be creative with FOR XML and PATH.

[Note: This solution only works on SQL 2005 and later. Original question didn't specify the version in use.]

CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)

SELECT 
  [ID],
  STUFF((
    SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) 
    FROM #YourTable 
    WHERE (ID = Results.ID) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID

DROP TABLE #YourTable

edited May 23 '17 at 11:58

Community

1
1

answered May 01 '15 at 19:33

Rocketq

5,423
23
75
126

Thanks both of you, it works! But frankly, I do not understand WHY! – Tiger955 May 01 '15 at 19:40
@Tiger955 keep in mind that this only performs well with a low amount of rows. Also look into FOR XML Path, and the STUFF command to understand how it works. – ZeRaTuL_jF May 01 '15 at 19:44
@ ZeRaTuL_jF: thanks, the number of rows will not exceed 20 per run. And YES, I already found the function STUFF and FOR XML Path. Great new experience for me! Thanks – Tiger955 May 01 '15 at 20:05
@Tiger955 Please, can you pick the answer) – Rocketq May 03 '15 at 11:28

score 0 · Answer 3 · answered May 01 '15 at 19:50

Pretty the same, just less letters in the code:

DECLARE @t AS TABLE ( id INT, msg VARCHAR(100) );

INSERT  INTO @t
VALUES     ( 2, 'give' );
INSERT  INTO @t
VALUES        ( 2, 'me' );
INSERT  INTO @t
VALUES        ( 2, 'help' );
INSERT  INTO @t
VALUES        ( 3, 'Need' );
INSERT  INTO @t
VALUES        ( 3, 'help' );
INSERT  INTO @t
VALUES        ( 1, 'help!' );

SELECT DISTINCT
    id ,
    (
      SELECT ST1.msg + ' '
      FROM @t ST1
      WHERE ST1.id = ST2.id
    FOR XML PATH('')
    ) t
FROM
    @t ST2;

Iterating through a SQL Server 2008 R2 table variable and concatenate values

3 Answers3