Linear Lines in SQL
I am looking for a way out, for example:
[last] date: comment, [second time] 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
Configuring MS SQL Server 2012 schema :
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');
Request 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, |
+3
source to share
1 answer
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 |
+3
source to share