Duplicate data in unique rows, show blank instead

Question

I have these tables:

CREATE TABLE [dbo].[Books] (
       [id]           int IDENTITY(1, 1) NOT NULL
     , [Title]        varchar(1000)      NOT NULL
     , [Publisher]    varchar(1000)          NULL
     , [ISBN]         varchar(50)            NULL
     , [Pages]        int                    NULL
     , [Date]         date                   NULL
     , [Plot_Summary] varchar(MAX)           NULL)
ON [PRIMARY]

CREATE TABLE [dbo].[Characters] (
       [id]   int IDENTITY(1, 1) NOT NULL
     , [Name] varchar(1000)      NOT NULL)
ON [PRIMARY]

CREATE TABLE [dbo].[Book_Char] (
       [id]      int IDENTITY(1, 1) NOT NULL
     , [Book_id] int                NOT NULL
     , [Char_id] int                NOT NULL)
ON [PRIMARY]

When I use this query:

SELECT b.title        AS Title
     , c.[Name]       AS Char_Name
     , b.Plot_Summary AS Summary
  FROM Books           b
 INNER JOIN Book_Char  bc ON b.id = bc.Book_id
 INNER JOIN Characters c  ON c.id = bc.Char_id

naturally, I get this result:

Title   Char_Name  Summary
---------------------------
title1  Name1      Summary1
title1  Name2      Summary1
title1  Name3      Summary1
title2  Name1a     Summary2
title2  Name2a     Summary2
title2  Name3a     Summary2

What I want is this:

Title   Char_Name  Summary
---------------------------
title1  Name1      Summary1
        Name2
        Name3
title2  Name1a     Summary2
        Name2a
        Name3a

My attempt at a union:

SELECT ''             AS Title
     , c.[Name]       AS Char_Name
     , ''             AS Summary
  FROM Books           b
 INNER JOIN Book_Char  bc ON b.id = bc.Book_id
 INNER JOIN Characters c  ON c.id = bc.Char_id
UNION
SELECT b.title
     , ''
     , b.Plot_Summary
  FROM Books           b
 INNER JOIN Book_Char  bc ON b.id = bc.Book_id
 INNER JOIN Characters c  ON c.id = bc.Char_id

gives:

Title   Char_Name  Summary
---------------------------
        Name1
        Name1a
        Name2
        Name2a
        Name3
        Name3a
title1             Summary1
title2             Summary2

I am new to unions, and I don't even know if that's the right answer here. I need to understand this; I'm not just looking for the codez. How can I do this, and how does it work?

Answer 1

Another option if you are using SQL Server 2012 or later is to use the LAG() function to "peek" at the last value. I would also recommend sequencing on IDs rather than values, because you may have books or characters with duplicate values. Fiddle available here.

SELECT
   [Title]      = B.Title
 , [Name]       = C.Name
 , [Summary]    = B.Plot_Summary
 , [dTitle]     = CASE WHEN LAG( BC.Book_id, 1 ) OVER ( ORDER BY BC.Book_id, BC.Char_id ) = BC.Book_id
                       THEN ''
                       ELSE B.Title
                  END
 , [dName]      = CASE WHEN LAG( BC.Char_id, 1 ) OVER ( ORDER BY BC.Book_id, BC.Char_id ) = BC.Char_id
                       THEN ''
                       ELSE C.Name
                  END
 , [dSummary]   = CASE WHEN LAG( BC.Book_id, 1 ) OVER ( ORDER BY BC.Book_id, BC.Char_id ) = BC.Book_id
                       THEN ''
                       ELSE B.Plot_Summary
                  END
FROM dbo.Books              AS B
INNER JOIN dbo.Book_Char    AS BC   ON b.id = bc.Book_id
INNER JOIN dbo.Characters   AS C    ON c.id = bc.Char_id
ORDER BY BC.Book_id, BC.Char_id

Answer 2

You can do what you want in the database. However, such presentation considerations are often better done at the application layer. Here is one method:

with bc as (
      SELECT b.title AS Title, c.[Name] AS Char_Name, b.Plot_Summary AS Summary
      FROM Books b INNER JOIN
           Book_Char bc
           ON b.id = bc.Book_id INNER JOIN
           Characters c
           ON c.id = bc.Char_id
     )
select (case when seqnum = 1 then bc.title else '' end) as title, bc.Char_name,
       (case when seqnum = 1 then bc.Summary else '' end) as Summary
from (select bc.*, row_number() over (partition by title order by char_name) as seqnum
      from bc
     ) bc
order by bc.title, bc.char_name;

The real problem with your query, though, is that you are expecting results in a particular order. You only get results in a particular order (guaranteed) when you use order by. And, your query has no order by.