SQL Server combine rows and columns

Question

please help, I'm starting to learn sql and I have to combine all 3 authors in one row.

how can I remove the dups and only show one result?

    with cte_authors (titleID, Authors, lvl)
    as
    (select distinct titles.title_id, convert(nvarchar(max), au_fname + ' ' + au_lname) as Authors , 1 as lvl 
from  titles inner join 
titleauthor on titles.title_id = titleauthor.title_id inner join
authors on titleauthor.au_id = authors.au_id
where au_ord > 2 
    union all   
    select titleauthor.title_id, Authors + '\' + au_fname + ' ' + au_lname , lvl + 1 
        from titleauthor inner join 
             cte_authors on cte_authors.titleID = titleauthor.title_id inner join
             titles on titles.title_id = cte_authors.titleID inner join


             authors on authors.au_id = titleauthor.au_id
                where lvl< 3)

    Select * from cte_authors
    group by titleID, Authors, lvl
    order by lvl

Result:

titleID               Authors                           lvl
TC7777  Burt Gringlesby                                 1
TC7777  Burt Gringlesby\Akiko Yokomoto                  2
TC7777  Burt Gringlesby\Burt Gringlesby                 2
TC7777  Burt Gringlesby\Michael O'Leary                 2
TC7777  Burt Gringlesby\Akiko Yokomoto\Akiko Yokomoto   3
TC7777  Burt Gringlesby\Akiko Yokomoto\Burt Gringlesby  3
TC7777  Burt Gringlesby\Akiko Yokomoto\Michael O'Leary  3
TC7777  Burt Gringlesby\Burt Gringlesby\Akiko Yokomoto  3
TC7777  Burt Gringlesby\Burt Gringlesby\Burt Gringlesby 3
TC7777  Burt Gringlesby\Burt Gringlesby\Michael O'Leary 3
TC7777  Burt Gringlesby\Michael O'Leary\Akiko Yokomoto  3
TC7777  Burt Gringlesby\Michael O'Leary\Burt Gringlesby 3
TC7777  Burt Gringlesby\Michael O'Leary\Michael O'Leary 3

result I need:

titleID               Authors                           lvl
TC7777  Burt Gringlesby\Michael O'Leary\Akiko Yokomoto  3

I'm sorry for the inconveniences. ive added the required result — Negar_Yuki, Jul 03 '19 at 18:12
What about "how can I remove the dups"? I don't see duplicates n the resultset. — Jeroen Heier, Jul 03 '19 at 20:07

score 1 · Answer 1 · answered Jul 03 '19 at 18:23

If you only need one row with a concatenated string of all the authors, you don't need a CTE.

SELECT titles.title_id, 
    STUFF((
            SELECT '\' + a.au_fname + ' ' + a.au_lname 
            FROM dbo.titleauthor ta 
            INNER JOIN dbo.authors a ON a.au_id = ta.au_id 
            WHERE ta.title_id = titles.title_id 
            ORDER BY ta.au_ord DESC
            FOR XML PATH('')
        ), 1, 1, '') AS Authors,
    (SELECT MAX(au_ord) FROM dbo.titleauthor WHERE title_id = titles.title_id) lvl
FROM titles

Returns:

title_id    Authors                                         lvl
TC7777      Burt Gringlesby\Michael O'Leary\Akiko Yokomoto  3

SQL Server combine rows and columns

1 Answers1