Looping lines in SQL

Question

I am looking for an output like:

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

Example:

Book    Comments
BookA   27/03/13: comment1, 21/03/13: comment2, 21/03/13: uhuuuu [and so 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 INTO 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 + ', '
FROM 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,  |

You're probably better off doing this in the application logic rather than in a SQL query. Essentially a duplicate of http://stackoverflow.com/questions/6899/is-there-a-way-to-create-a-sql-server-function-to-join-multiple-rows-from-a-su — p.s.w.g, Nov 06 '14 at 23:13
Doing this in SQL is what I am looking for. Actually I do this in app logic. Thanks. — Khrys, Nov 06 '14 at 23:15
Sql fiddle is a great tool and well done for posting using it. It would be even better if you used the arrow on the run button to select the markdown output, which formats your code into a format that you can paste straight into a question, with all of the correct formatting and links included. This way it's easier for users to see you question without visiting the external site, plus that link might rot or be unavailable in the future. — Tanner, Nov 06 '14 at 23:17
Glad to help, that's immediately made your post much more attractive to users. Please share the advice with other users if you have the opportunity. — Tanner, Nov 06 '14 at 23:23

sqlhdv · Accepted Answer · 2014-11-06T23:37:03.340

SQL Fiddle

SELECT book,
       Stuff((SELECT ', ' + ltrim(rtrim(CONVERT(VARCHAR, datewww, 3) + ': ' + comments))
              FROM   books t2 
              WHERE  t2.book = t1.book
              order by datewww desc
               FOR XML PATH(''), TYPE
                   ).value('.','varchar(max)')
                   ,1,2, '') [comments] 
FROM   books t1 
GROUP  BY 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 |

Thanks for your help. I am trying to apply the code to my scenario but it has more tables and it isn't working. Still trying here... — Khrys, Nov 07 '14 at 10:49

Looping lines in SQL

1 Answers1

Linked