sql server - Looping lines in SQL -

sql server - Looping lines in SQL -

i looking output like:

[most recent]date: comment, [second recent]date: comment,...

example:

book comments booka 27/03/13: comment1, 21/03/13: comment2, 21/03/13: uhuuuu [and on] bookb 21/03/13: comment2a, 18/03/13: xxx comments

sql fiddle

ms sql server 2012 schema setup:

create table books ( book varchar(10), comments varchar(20), datewww datetime ); insert books (book, comments, datewww) values ('booka', 'comment1', '2013-03-27 10:30:00.000'), ('booka', 'comment2', '2013-03-21 09:31:00.000'), ('booka', 'comentx', '2013-03-10 08:31:00.000'), ('booka', 'text test', '2013-02-15 07:41:00.000'), ('booka', 'uhuuuu', '2013-03-21 07:31:00.000'), ('bookb', 'comment2a', '2013-03-21 09:31:00.000'), ('bookb', 'xxx comments', '2013-03-18 09:31:00.000');

query 1:

select book, convert(varchar, datewww, 3) + ': ' + comments + ', ' books

results:

| book | column_1 | |-------|--------------------------| | booka | 27/03/13: comment1, | | booka | 21/03/13: comment2, | | booka | 10/03/13: comentx, | | booka | 15/02/13: text test, | | booka | 21/03/13: uhuuuu, | | bookb | 21/03/13: comment2a, | | bookb | 18/03/13: xxx comments, |

sql fiddle

select book, stuff((select ', ' + ltrim(rtrim(convert(varchar, datewww, 3) + ': ' + comments)) books t2 t2.book = t1.book order datewww desc xml path(''), type ).value('.','varchar(max)') ,1,2, '') [comments] books t1 grouping book

results:

| book | comments | |-------|--------------------------------------------------------------------------------------------------| | booka | 27/03/13: comment1, 21/03/13: comment2, 21/03/13: uhuuuu, 10/03/13: comentx, 15/02/13: text test | | bookb | 21/03/13: comment2a, 18/03/13: xxx comments |

sql sql-server