Get N lines concatenated in 1 line using FOR XML

Question

I am looking for an output like:

|  BOOK | DETAILS                                                           |
|-------|-------------------------------------------------------------------|
| BookA | COD_100,A,mary.jane||COD_101,P,silvia.poff                        |
| BookB | COD_102,A,andrea.maya                                             |

What I am getting is all the results for all the lines, instead, like:

|  BOOK |  DETAILS                                                          |
|-------|-------------------------------------------------------------------|
| BookA | COD_100,A,mary.jane||COD_101,P,silvia.poff||COD_102,A,andrea.maya |
| BookB | COD_100,A,mary.jane||COD_101,P,silvia.poff||COD_102,A,andrea.maya |

I had a question (Looping lines in SQL) almost the same, but I couldn't reproduce it in this case. Bellow is the the Fiddle I used to get the data:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE books 
  (
    book varchar(10),
    book_group varchar(10),
    status varchar(10)
  );

INSERT INTO books (book, book_group, status)
VALUES
('BookA', 'A', 'open'),
('BookB', 'A', 'open'),
('BookC', 'B', 'open'),
('BookD', 'C', 'open'),
('BookE', 'D', 'open');

CREATE TABLE books_underanalisys 
  (
    book varchar(10)
  );

INSERT INTO books_underanalisys (book)
VALUES
('BookA'),
('BookB'),
('BookC'),
('BookD'),
('BookE');

CREATE TABLE books_underanalisys_PlanA
  (
    book varchar(10),
    ID_books_underanalisys_PlanA varchar(10)
  );

INSERT INTO books_underanalisys_PlanA (book, ID_books_underanalisys_PlanA)
VALUES
('BookA', '100'),
('BookA', '101'),
('BookB', '102'),
('BookC', '103'),
('BookC', '104');


CREATE TABLE books_underanalisys_PlanA_detail 
  (
    ID_books_underanalisys_PlanA_detail varchar(10),
    COD_books_underanalisys_PlanA_detail varchar(10),
    status varchar(1),
    username varchar(100)
  );

INSERT INTO books_underanalisys_PlanA_detail (ID_books_underanalisys_PlanA_detail, COD_books_underanalisys_PlanA_detail, status, username)
VALUES
('100', 'COD_100', 'A', 'mary.jane'),
('101', 'COD_101', 'P', 'silvia.poff'),
('102', 'COD_102', 'A', 'andrea.maya'),
('103', 'COD_103', 'A', 'johan.kin'),
('104', 'COD_104', 'P', 'hingo.trunk');

Query 1:

SELECT
    books_underanalisys .book AS Book,
    STUFF((
SELECT
    '||' + books_underanalisys_PlanA_detail.COD_books_underanalisys_PlanA_detail + ',' + books_underanalisys_PlanA_detail.status + ',' + books_underanalisys_PlanA_detail.username AS [text()]
FROM
    books_underanalisys_PlanA right join books_underanalisys_PlanA_detail on books_underanalisys_PlanA.ID_books_underanalisys_PlanA = books_underanalisys_PlanA_detail.ID_books_underanalisys_PlanA_detail,
    books_underanalisys ,
    books
WHERE
    books_underanalisys_PlanA.book = books_underanalisys .book
AND books_underanalisys_PlanA.book = books.book
AND books.book_group = 'A'
AND books.status <> 'closed'
FOR XML PATH('')), 1, 2, '' ) AS DETAILS
FROM
    books_underanalisys_PlanA right join books_underanalisys_PlanA_detail on books_underanalisys_PlanA.ID_books_underanalisys_PlanA = books_underanalisys_PlanA_detail.ID_books_underanalisys_PlanA_detail,
    books_underanalisys ,
    books
WHERE
    books_underanalisys_PlanA.book = books_underanalisys .book
AND books_underanalisys_PlanA.book = books.book
AND books.book_group = 'A'
AND books.status <> 'closed'
GROUP BY
    books_underanalisys .book

Results:

|  BOOK |                                                           DETAILS |
|-------|-------------------------------------------------------------------|
| BookA | COD_100,A,mary.jane||COD_101,P,silvia.poff||COD_102,A,andrea.maya |
| BookB | COD_100,A,mary.jane||COD_101,P,silvia.poff||COD_102,A,andrea.maya |

score 3 · Accepted Answer · answered Dec 08 '14 at 13:33

3

Looks like you need something like this:

select b.book,
       (
       select '||'+d.COD_books_underanalisys_PlanA_detail+','+d.status+','+d.username
       from books_underanalisys_PlanA as a
         inner join books_underanalisys_PlanA_detail as d
           on a.ID_books_underanalisys_PlanA = d.ID_books_underanalisys_PlanA_detail
       where a.book = b.book
       for xml path(''), type
       ).value('substring(text()[1], 3)', 'varchar(max)') as details
from books as b
where b.book_group = 'A' and
      b.status <> 'closed'

answered Dec 08 '14 at 13:33

Mikael Eriksson

136,425
22
210
281

This is it! Thanks a lot. I end up needing another information. Instead of the username, I need to show the fullname. Fiddle here: http://sqlfiddle.com/#!3/c8c9c/1. Can you help me again? Thanks. – Khrys Dec 08 '14 at 16:50
Join to `books_analysts` in the subquery on `username` and add `fullname` to the string you are concatenating. – Mikael Eriksson Dec 08 '14 at 17:29
1

You need to study how joins work. You are mixing old style joins using comma with ansi joins. Look up the syntax for ansi joins and perhaps a couple of blogposts describing what a join is. This query is way out of your depth without that knowledge and you should **never** use queries from the Internet without fully understanding what they do. For all you know this query might bring havoc on your poor server. – Mikael Eriksson Dec 08 '14 at 17:58
I honestly agree with you. But as you helped me before, can you help me again in this case? Thanks. – Khrys Dec 09 '14 at 10:54

Get N lines concatenated in 1 line using FOR XML

1 Answers1